Get detailed information about SQL statements

Oracle9i offers some great tools to make it easy to view the execution plan and trace information for a 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.

We can now 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. It will also provide the execution plan for the SQL statement. Execution plans for Oracle SQL can be very complex, so you should consult references for complete instructions on how to interpret and tune SQL statement execution plans for more information.

SET AUTOTRACE ON
This command will provide detailed statistics for the Oracle SQL. It will 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. This phase can be quite high for queries with more than five tables, unless you're using ORDERED or RULE HINTS.

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 Oracle professionals 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 can make SQL that executes very quickly.

Donald Burleson has been a database administrator for 23 years and has written 14 database books and over 100 articles. He is editor in chief of Oracle Internals and runs Burleson Oracle Consulting.