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

Redneck
 

Fast and easy SQL internals plan in Oracle9i

 

Donald K. Burleson

 

Oracle9i now has some great tools to make it easy to view the execution plan and trace information for an SQL statement.  Prior to Oracle8i, you had to create a plan table, and execute special syntax to view the execution plan for SQL.  For detailed statistics, you had to run the TKPROF utility.

Today, we can get the execution plan and trace information for any SQL statement very quickly and easily with a few SQL*Plus commands.

Set autotrace on explain - Running this SQL*Plus directive will execute your SQL query and also provide the execution plan for the SQL statement. Execution plans for Oracle SQL can be very complex, but the Oracle Press book “Oracle High-performance SQL tuning” provides complete instruction on how to interpret and tune SQL statement execution plans.

Set autotrace on - The “set autotrace on” command will provide detailed statistics for the Oracle SQL and show the amount of time spent parsing, executing and fetching rows. The parse phase is the time spent by the query determining the optimal execution plan, and this phase can be quite high for queries with more than 5 tables unless the ORDERED or RULE HINTS are used.  The execution phase is the time spent executing the query, and the fetch phase is the time spent returning the rows to the query.

These new SQL*Plus directives can make it very easy for the Oracle professional to ensure that their SQL statements are properly tuned.  Remember, the hallmark of a good developer is someone who can not only make SQL statements, but make SQL that executes very quickly.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2003_1_oracle9i_sga.htm

 

Oracle DBA, Oracle Consulting
  
 

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 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.