Oracle Execution Plan tips Oracle Database Tips by Donald Burleson

An Oracle execution plan (explain plan) is a way to see the internal row fetching mechanisms that are used to retrieve the rows from an SQL statement.  As author of the officially authorized Oracle Press book "Oracle High Performance SQL Tuning", I've been using execution plans for over a decade, and I can say that tuning SQL can be very difficult without understanding SQL execution plans.

My latest book book "Oracle Tuning: The Definitive Reference", is a great way to understand SQL tuning with SQL execution plans, and I highly recommend it for SQL tuning with execution plans.

Displaying an execution plan

For any SQL statement you can quickly see the execution plan in SQL*Plus with the "set autotrace" command, followed by your 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)

Oracle execution plan methods

When you display an Oracle execution plan, you will see many tables access methods, including TABLE ACCESS FULL, INDEX RANGE SCAN, INDEX UNIQUE SCAN, and many, many others including LOOP execution plans such as NESTED LOOP, MERGE and HASH JOIN.  It's important to understand these execution plan steps so that you understand how your tables are being accessed by Oracle.

System-wide execution plans

To see an aggregation of all SQL access methods in your database with counts, see plan9i.sql, a great way to locate missing indexes and see how your tables are being accessed by the SQL, and to locate indexing opportunities:

                     Full table scans and counts
      Note that "K" indicates in the table is in the KEEP pool.

OWNER          NAME                      NUM_ROWS  C K   BLOCKS  NBR_FTS
-------------- ------------------------  --------- - - -------- --------
SYS            DUAL                                N          2   97,237
SYSTEM         SQLPLUS_PRODUCT_PROFILE             N K        2   16,178
DONALD         PAGE                      3,450,209 N    932,120    9,999
DONALD         RWU_PAGE                        434 N          8    7,355
DONALD         PAGE_IMAGE                   18,067 N      1,104    5,368
DONALD         SUBSCRIPTION                    476 N K      192    2,087
DONALD         PRINT_PAGE_RANGE                 10 N K       32      874

For time-series execution plan observation, see the script plan10g.sql.  For even more detailed SQL execution statistics, see the TKPROF (SQL Trace) utility.

More information on creating and reading Oracle SQL execution plans is available 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.