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 SQL Execution Plan tips

Oracle Database Tips by Donald Burleson

Understanding a SQL execution plan is critical to SQL tuning as I note in my book
"Oracle Tuning: The Definitive Reference".

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

The execution plan reveals the internal details about the SQL execution including table join types (nested loops, hash join, merge join), and sorting.

Creating an Execution 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 to see the execution plan:

SQL> set autotrace traceonly explain
SQL> select ename from emp where empno = 12;

Execution Plan

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

Reading an Oracle Execution Plan

For a full tutorial, get my book  "Oracle Tuning: The Definitive Reference" where I've devoted over 100 pages to reading execution plans.  My goals for SQL tuning with execution plans are simple, "Fetch the rows while minimizing data block touches":

  • Eliminate sub-optimal large-table full-table scans: Ensure that the fastest access path to the data is chosen.  The execution plan "FULL" is the key.
  • Use fastest table join method: The optimizer must choose intelligently between nested loops joins, hash joins and star transformation join methods and these are displayed in the execution plan.
  • Ensure optimal table-joining order: SQL will run fastest when the first table joins deliver the smallest result set.

In sum, I review execution plans looking for tuning opportunities as follows:

  • Add indexes (especially function-based indexes)
  • Change the optimizer parms 
  • Employ Materialized Views
  • Improve CBO statistics (especially histograms)
  • Cache high-use small tables & indexes
  • Tune the library cache (cursor_sharing, etc)
  • Adjust segment structure (large blocksizes for index range scans, small
  •  blocksizes for random OLTP)

Global Tuning with Execution 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).

More information on Oracle 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.