Oracle explain plan tips Oracle Database Tips by Donald Burleson

The "explain plan" utility is provided by Oracle to allow you to see the internal machinations of how your SQL is executed.  Using the "explain plan" to solve SQL tuning problems is central in my book "Oracle Tuning: The Definitive Reference".

Also see how to determine the sequence of the steps in an explain  plan.

The explain plan shows table access methods (FULL, INDEX), table join types (nested loops, hash join, merge join), and sorting.

Creating a SQL explain plan

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 = 12;

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.

Most relational databases use an explain utility that takes the SQL statement as input, runs the SQL optimizer, and outputs the access path information into a plan_table, which can then be interrogated to see the access methods.

EXPLAIN PLAN SET STATEMENT_ID = 'test1' FOR
SET STATEMENT_ID = 'RUN1'
INTO plan_table
FOR
SELECT

For even more detailed SQL execution statistics, see the TKPROF (SQL Trace) utility.

Reading an explain plan

For a full tutorial, get my book  "Oracle Tuning: The Definitive Reference" where I've devoted over 100 pages to reading execution plans.  The most common way of determining the execution plan for a SQL statement is to use Oracle's explain plan utility.

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.

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.