Part II
SQL-Related Performance Tools
Part II discusses Oracle's SQL-related performance tools. These tools examine the execution plan for a SQL statement, and 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.
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:
