Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








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

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.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.