Database Reference
In-Depth Information
bind variable values. Thus, it's a good idea to capture plans with AWR snapshots or level 6
Statspack snapshots.
The SQL statement
EXPLAIN
PLAN
may be used to generate an execution plan for SQL state-
ments that include bind variables. However,
EXPLAIN
PLAN
knows nothing about the bind variable
data types and values. You need not even declare SQL*Plus variables to successfully use
EXPLAIN
PLAN
on a statement that includes bind variables.
EXPLAIN
PLAN
is notoriously unreliable. It should never be used for statement tuning, since
it regularly reports execution plans that are different from the actual plan used when the state-
ment is executed. The
Oracle Database Performance Tuning Guide 10g Release 2
has this to say
about
EXPLAIN
PLAN
(page 19-4):
Oracle does not support EXPLAIN PLAN for statements performing implicit type
conversion of date bind variables. With bind variables in general, the EXPLAIN PLAN
output might not represent the real execution plan.
SQL trace files,
V$SQL_PLAN
,
V$SQL_PLAN_STATISTICS_ALL
, AWR, and the Statspack Repository
table
STATS$SQL_PLAN
, which contains snapshots of
V$SQL_PLAN
, are reliable sources for execu-
tion plans. Be warned that
V$SQL_PLAN
may hold several plans for the same
HASH_VALUE
or
SQL_ID
and there is no easy way of figuring out which plan was used. To avoid this pitfall, it is
possible to run
tail
-f
on the trace file to display the tail of the file as it grows. The unformatted
execution plan in
STAT
entries is rather awkward to read. The Oracle10
g
pipelined table function
DBMS_XPLAN.DISPLAY_CURSOR
has the most sophisticated solution to date. Its syntax is as follows:
dbms_xplan.display_cursor(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;
When
DBMS_XPLAN.DISPLAY_CURSOR
is called without passing a
sql_id
and
child_number
,
the plan of the last cursor executed by the session is displayed, making the function an ideal
replacement for SQL*Plus
AUTOTRACE
. The format argument allows precise control of the output.
The most verbose output pertaining merely to the last execution of a statement is obtained by
using the format
'ALL
ALLSTATS
LAST'
. To collect memory and I/O statistics,
STATISTICS_LEVEL=ALL
must be set at session level. The columns “A-Rows” (actual rows), “A-Time” (actual time),
“Buffers”, and “Reads”, which are present in the example that follows, are missing at the default
STATISTICS_LEVEL=TYPICAL
. The subsequent example illustrates several points:
Flushing the buffer cache to provoke disk reads, which affects the values reported in
column “Buffers” of the execution plan generated with
DBMS_XPLAN.DISPLAY_CURSOR.
The use of SQL*Plus variables as bind variables in SQL statements.
The impact of the optimizer environment, specifically the parameter
OPTIMIZER_INDEX_
COST_ADJ
on execution plans.
How to retrieve filter and access predicates along with an execution plan. Columns in a
WHERE-
clause are called predicates.
How to retrieve query block names, which may be useful for tuning with optimizer hints.
