Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Reading an Execution Plan
Oracle Tips by Burleson
 

Execution plans are often very difficult to interpret. While the beginner can scan the execution plan looking for TABLE ACCESS FULL, the more sophisticated experts closely examine the order of execution within an execution plan. In general, execution plans are read inside-out, starting with the most indented operation. Here are some general rules for reading an explain plan.

1.      The first statement is the one that has the most indentation.

2.      If two statements appear at the same level of indentation, the top statement is executed first.

To see how this works, let’s reformat the execution plan from before to make the steps easier to see. Before you read on, see if you can place the steps in the actual order of execution.

SELECT STATEMENT                                                               
  SORT ORDER BY                                              
    NESTED LOOPS                                                                
      NESTED LOOPS                                                             
        TABLE ACCESS BY INDEX ROWID         SUBSCRIPTION                           
          BITMAP CONVERSION TO ROWIDS                                                              
            BITMAP INDEX FULL SCAN          SUBSC_PUB_NAME_IDX       
        TABLE ACCESS BY INDEX ROWID         BOOK                                   
          INDEX UNIQUE SCAN                 BOOK_ISBN                               
      TABLE ACCESS BY INDEX ROWID           PAGE                                   
        INDEX UNIQUE SCAN                   ISBN_SEQ_IDX

The indentation is clear, so we can work from inside-out, top-down and arrive at the following sequence of steps:

1.      Bitmap index full scan on the subsc_pub_name_idx index

2.      Bitmap conversion to ROWIDs

3.      Index unique scan on the book_isbn index

4.      Table access by ROWID on the subscription table

5.      Table access by ROWID on the book table

6.      Index unique scan on the isbn_seq_idx index

7.      Nested Loops scan of the result set

8.      Table access by ROWID on the page table

9.      Nested loop scan of the result set

10.  Sorting the final result set

Hopefully, this will give you enough understanding to accurately know the order of the execution of steps in a SQL execution plan.

Also, please be aware that the execution plan output from explain plan is very different than the execution plan as stored in the ol$hints table. As you may know, in Oracle8i we have optimizer plan stability that is implemented via the Oracle create outline command. To see the difference, refer to Chapter 13, or execute the outline.sql script from the Oracle Press Web site.

For now, do not be concerned if you do not understand all of the different types of table access methods. I will be covering these access methods on an as-needed basis when we delve into the tuning details in subsequent chapters.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter