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