Part II
SQL-Related Performance Tools

Oracle's SQL-related performance tools let you examine the execution plan for a SQL statement, and thereby determine whether the statement can be better optimized. You can get the execution plan from the EXPLAIN PLAN SQL statement, from querying V$SQL_PLAN, or from SQL trace.

In the development phase, use EXPLAIN PLAN to determine a good access plan, and then verify that it is the optimal plan through volume data testing. When evaluating a plan, examine the statement's actual resource consumption using V$SQLAREA with V$SQL_PLAN, Oracle Trace, or the SQL trace facility and TKPROF. The information in the V$SQL_PLAN view is very similar to the output of an EXPLAIN PLAN statement. However, EXPLAIN PLAN shows a theoretical plan that can be used if the statement were to be executed, whereas V$SQL_PLAN contains the actual plan used. Hence, querying V$SQLAREA in conjunction with V$SQL_PLAN provides similar results to using SQL Trace with TKPROF.

See Also: Chapter 24, "Dynamic Performance Views for Tuning"

The autotrace tool lets you automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. It is useful for monitoring and tuning the performance of these statements. Oracle Trace is a GUI, event-driven data collection product, which the Oracle server uses to collect performance and resource utilization data.

The chapters in this part are:

• Chapter 9, "Using EXPLAIN PLAN"
• Chapter 10, "Using SQL Trace and TKPROF"
• Chapter 11, "Using Autotrace in SQL*Plus"
• Chapter 12, "Using Oracle Trace"