Inside Oracle execution plans
Oracle Database Tips by Donald Burleson
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;
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
sqlplus > @utlxplan
sqlplus > create public synonym plan_table for sys.plan_table;
Warning! - Explain plan gives wrong
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?
Global tuning with
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
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.