Skip Headers

Oracle9i Database Performance Tuning Guide and Reference
Release 2 (9.2)

Part Number A96533-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF

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.

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: