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

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Concepts by Burleson Consulting

Viewing SQL Execution Plans

In this exercise you will learn how to view execution plan details and view SQL execution statistics. In almost all relational databases, a special table called plan_table is used to store the execution plan for an SQL statement.  To give a simple example, the following command will create a plan_table, populate the table with the execution plan for a query, and then display the contents of the plan table:

SQL> @c:\oracle\ora8i\rdbms\admin\utlxplan

Table created.

SQL> set autotrace on explain

SQL> select * from author;

Wed May 29                                                             page    1
                                  Book Report

AUTHOR_KEY  AUTHOR_LAST_NAME                         AUTHOR_FIRST_NAME         
----------- ---------------------------------------- --------------------      
AUTHOR_PHONE AUTHOR_STREET                            AUTHOR_CITY          AU  
------------ ---------------------------------------- -------------------- --  
AUTHO AUTHOR_CONTRACT_NBR                                                      
----- -------------------                                                       
A101        Burleson                                  mark                      
303-462-1222 1401 west fourth st                      st. louis            MO   47301                5601

A102        hester                                    alvis                     
523-882-1987 2503 backer view st                      st. louis            MO  
47301                5602                                                      

A103        weaton                                    erin                      
367-980-8622 6782 hard day dr                         st. louis            MO
47301                5603                                                      

A104        jeckle                                    pierre                    
543-333-9241 3671 old fort st                         north hollywood      CA
91607                6602                                                      

A105        withers                                   lester
457-882-2642 1320 leaning tree ln                     pie town             IL
57307                7896                                                       

A106        petty                                     juan                      
344-455-6572 8869 wide creek rd                       happyville           TX
77304                6547                                                      

A107        clark                                     louis
666-555-8822 7980 shallow pond st                     rose garden          WI
33301                3452                                                      

A108        mee                                       minnie                    
321-543-9876 2356 empty box rd                        belaire              KY  
45461                7954                                                      

A109        shagger                                   dirk
987-654-3210 3452 dirt path way                       cross trax           LA   7301                   1                                                       

A110        smith                                     diego
564-897-3201 2567 south north st                      tweedle              MA
47301                2853                                                      

10 rows selected.

Execution Plan
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=10 Bytes=         

   1    0   TABLE ACCESS (FULL) OF 'AUTHOR' (Cost=1 Card=10 Bytes=660)         

          0  recursive calls                                                    
          2  db block gets                                                     
          2  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
       2187  bytes sent via SQL*Net to client                                  
        503  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
         10  rows processed  

Step 1 – Create a plan table - The first step in this exercise is to connect as the pubs user and create a plan table. 

SQL> connect pubs/pubs
SQL> @c:\oracle\ora8i\rdbms\admin\utlxplan

Remember, you must be connected as the pubs user in order to complete this exercise.  If you've done the exercise correctly you should see the following output:

Table created.

Step 2 – Build a test script - Now that we have a plan table in place, we are now ready to use the autotrace facility in order to display the execution plan for query.  There are three forms of the autotrace command, and your challenge is to try these commands with a query and describe the differences between the commands. Place these commands in a filed called trace_me.sql and execute the query:

spool t.lst

set autotrace on explain; 

select * from author;

set autotrace on explain only;

select * from author;

set autotrace on;

select * from author;

host notepad t.lst

Run this script using each one of these three operators and note the differences between these three permutations of the autotrace command.  The point of this exercise is to note the different permutations of the autotrace command and see how some autotrace commands execute the query, while others serve only to provide statistics for the individual query.

Step 5 – Submit Listing - Submit the listing to your instructor with a discussion of the different autotrace options

Now that we understand how to generate and execution plan and a trace plan for an SQL query we are now ready to do more sophisticated analysis.

For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning book



Oracle performance tuning software

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


email BC:

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

Oracle® is the registered trademark of Oracle Corporation.