Databases Reference
In-Depth Information
most to elapsed time with TKPROF, you might want to sort the statements with the option
fchela
(fetch elapsed time). Fetch elapsed time includes fetch CPU time.
Execution Plan Hash Value
Starting with Oracle9
i
, execution plans were made available through the V$ views
V$SQL_PLAN
and
V$SQL_PLAN_STATISTICS_ALL
. Formerly, execution plans had been available in SQL trace
files only. Multiple execution plans may be used for a single SQL statement both over time and
simultaneously. Varying optimizer statistics, differences in parameters related to the cost
based optimizer (a.k.a. optimizer environment), and adaptive cursor sharing introduced in
Oracle11
g
are potential causes for multiple plans. Release 11.1.0.7 (Oracle11
g
patch set 1) is the
first release that includes the execution plan hash value in SQL trace files. It is represented by
the parameter
plh
of
PARSE
,
EXEC
, and
FETCH
entries (see Table 24-4). This new feature resolves
an issue with SQL trace files where multiple plans for a statement are present in the SGA and
no plan is emitted to the SQL trace file. Formerly, it could not be determined which of the plans
applied to the traced session. Since the inclusion of
plh
in release 11.1.0.7 it is trivial to retrieve
missing plans from
V$SQL_PLAN
. The new parameter also facilitates the retrieval of additional
information on an execution plan with the function
DBMS_XPLAN.DISPLAY_CURSOR
. This function
returns much more information on execution plans than is emitted to SQL trace files. In fact it
provides an unsurpassed level of detail that matches the extent of information produced by
event 10053. The signature of
DBMS_XPLAN.DISPLAY_CURSOR
is reproduced here:
FUNCTION display_cursor(sql_id VARCHAR2 DEFAULT NULL,
cursor_child_no INTEGER DEFAULT 0,
format VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table
PIPELINED;
The amount of detail returned by the function depends on the parameter
FORMAT
. To achieve
the same level of detail as the output from event 10053, the two undocumented format options
ADVANCED and PEEKED_BINDS need to be supplied. ADVANCED includes a set of hints that
fully describe an execution plan in a section titled Outline Data. As the name suggests, the
format option PEEKED_BINDS reports the values of peeked bind variables, unless bind variable
peeking is disabled with
_OPTIM_PEEK_USER_BINDS=FALSE
. The next example illustrates the use
of
DBMS_XPLAN.DISPLAY_CURSOR
based on the correlation between trace file entries and V$ views.
Plan Hash Value Case Study
A join between the sample schema tables EMPLOYEES and DEPARTMENTS is used in the
following code example to highlight the extent of information available through both SQL trace
and V$ views:
SQL> ALTER SESSION SET SQL_TRACE=TRUE;
Session altered.
SQL> VARIABLE dept_id NUMBER
SQL> VARIABLE emp_id NUMBER
SQL> VARIABLE fn VARCHAR2(30)
SQL> EXEC :dept_id:=50; :emp_id:=120; :fn:='Matthew'
SQL> SELECT emp.last_name, emp.first_name, d.department_name
