 |
|
Inside Oracle execution plans
Oracle Tips by Burleson Consulting |
For complete details on tuning SQL
with execution plans (explain plan), see
my book "Oracle
Tuning: The Definitive Reference". Also see my notes on tuning
historical SQL from STATSPACK and AWR.
The "explain plan" utility is provided by Oracle to allow you to see the
internal machinations of how your SQL is executed.
For any specific SQL query you can see SQL
execution plan. In SQL*Plus you can issue the "set autotrace" command
and then issue your SQL statement:
SQL> set autotrace traceonly explain
SQL> select ename from emp where empno = 22;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
To see the output of an explain
plan, you must first create a “plan table.” Oracle provides a script in $ORACLE_HOME/rdbms/admin
called utlxplan.sql. Execute utlxplan.sql and create a public synonym for the
plan_table:
sqlplus > @utlxplan
Table created.
sqlplus > create public synonym plan_table for sys.plan_table;
Synonym created.
Warning! - Explain plan gives wrong
details!
It’s possible that the standard relational “explain plan for”
syntax show a plan that is incorrect, and you may not see the execution plan
that is actually being implemented.
This commonly happens
when your query has a bind variable. This happens because the explain
plan utility does not consider the value of a host variable, while the
Oracle optimizer may consider a host variable (when bind variable peeking is
enabled with the 11g adaptive cursor sharing feature.
As a remedy,
you should always use the dbms_xplan utility and avoid using the
Oracle implementation of the ANSI standard “explain plan for querylist”
syntax..
Global tuning with
explain plans
My
plan9i and plan10g scripts
are invaluable for holistic SQL tuning because they examine v$sql_plan
and dba_hist_sql_plan and plot summary execution plans, identifying
possible unnecessary large-table full-table scans (that may result from a
missing index), and many, many other techniques.
See my other notes on reading SQL execution
plan here:
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
