Database Reference
In-Depth Information
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=TO_NUMBER(:B1))
It's generally good practice to check whether datatypes are correctly handled, for example, by using explicit
conversion for all bind variables of the original SQL statement that aren't of
VARCHAR2
type.
The second problem with using bind variables with the
EXPLAIN PLAN
statement is that no bind variable peeking
is used. Because there's no solution for that problem, it isn't guaranteed that the execution plan generated by the
EXPLAIN PLAN
statement will be the one chosen at runtime. In other words, whenever bind variables are involved, the
output generated by the
EXPLAIN PLAN
statement is unreliable.
Dynamic Performance Views
Four dynamic performance views show information about the cursors present in the library cache:
v$sql_plan
provides basically the same information as the plan table. In other words, it
provides execution plans and other related information provided by the query optimizer.
The only notable differences between this view and the plan table are due to some columns
identifying the cursor related to the execution plan in the library cache.
•
v$sql_plan_statistics
provides execution statistics, such as the elapsed time and the
number of produced rows, for each operation in the
v$sql_plan
view. Essentially, it provides
the runtime behavior of an execution plan. This is an important piece of information because
the
v$sql_plan
view shows only the estimations and decisions taken by the query optimizer
at parse time. Because the collection of execution statistics might cause a non-negligible
overhead (depending on the execution plan and the operating system of the database
server, the overhead can also be significant), by default they aren't collected. To activate the
collection, either the
statistics_level
initialization parameter must be set to
all
or the
gather_plan_statistics
hint must be specified in the SQL statement. Be aware that, because
of the possible overhead, I don't recommend changing the default value of the
statistics_level
initialization parameters at the system level.
•
•
v$sql_workarea
provides information about the memory work areas needed to execute
a cursor. It gives runtime memory utilization as well as estimations about the amount of
memory needed to efficiently execute operations.
•
v$sql_plan_statistics_all
shows in a single view all the information provided by the
v$sql_plan
,
v$sql_plan_statistics
, and
v$sql_workarea
views. By using it, you simply
avoid manually joining several views.
The cursors in the library cache (and therefore in these dynamic performance views) are identified by two
columns:
address
and
child_number
. With the
address
column, you identify the parent cursors. With the two
columns, you identify the child cursors. It's more common to use the
sql_id
column instead of the
address
column to
identify cursors. The advantage of using the
sql_id
column is that its value depends only on the SQL statement itself.
In other words, it never changes for a given SQL statement (in fact, it's the result of a hash function applied to the
text of the SQL statement). On the other hand, the
address
column is a pointer to the handle of the SQL statement in
memory and can change over time.
To identify a cursor, basically you're confronted with two search methods. Either you know the session executing
a SQL statement or you know the text of the SQL statement. In both cases, once the child cursor is identified, you can
display information about it.
