Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 
     Oracle: Viewing SQL execution plans

 
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
 
QL> 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        jones                                    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  
47301                   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=         
          660)                                                                  
                                                                               
   1    0   TABLE ACCESS (FULL) OF 'AUTHOR' (Cost=1 Card=10 Bytes=660)         
 
 
 
 
Statistics
----------------------------------------------------------                     
          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.
 
ANSWERS

 
  • Set autotrace on explain;  - This executes the query and then shows the execution plan.
 
 
  • Set autotrace on explain only; - This only shows the execution plan and does not run the query.
 
 
  • Set autotrace on; - This executes the query, shows the exe3cution plan and also displays the execution statistics for the query.”


 

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.