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;

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:

 

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle 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 buy it for 30% off directly from the publisher.