Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

4.8 Understanding the Execution Plan

Oracle's query optimizer automatically selects an
execution plan for each query submitted. By and large, although the
optimizer does a good job of selecting the execution plan, there may
be times when the performance of the database suggests that
it's using a less-than-optimal execution plan.

The only way you can really tell what path is being selected by the
optimizer is to see the layout of the execution plan. You can use two
Oracle character-mode utilities to examine the execution plan chosen
by the Oracle optimizer, or you can look at the execution plan in
Enterprise Manager, as of Oracle Database 10g. These tools allow you
to see the successive steps used by Oracle to collect, select, and
return the data to the user.

The first utility is the SQL EXPLAIN PLAN statement. When you use
EXPLAIN PLAN, followed by the keyword FOR and the SQL statement whose
execution plan you want to view, the Oracle cost-based optimizer
returns a description of the execution plan it will use for the SQL
statement and inserts this description into a database table. You can
subsequently run a query on that table to get the execution plan, as
shown in SQL*Plus in Figure 4-5.

Figure 4-5. Results of a simple EXPLAIN PLAN statement in SQL*Plus

The execution plan is presented as a series of rows in the table, one
for each step taken by Oracle in the process of executing the SQL
statement. The optimizer also includes some of the information
related to its decisions, such as the overall cost of each step and
some of the statistics that it used to make its decisions.

The optimizer writes all of this information to a table in the
database. By default, the optimizer uses a table called PLAN_TABLE;
make sure the table exists before you use EXPLAIN PLAN. (The
utlxplan.sql script included with your Oracle
database creates the default PLAN_TABLE table.) You can specify that
EXPLAIN PLAN uses a table other than PLAN_TABLE in the syntax of the
statement. For more information about the use of EXPLAIN PLAN, please
refer to your Oracle documentation.

There are times when you want to examine the execution plan for a
single statement. In such cases, the EXPLAIN PLAN syntax is
appropriate. There are other times when you want to look at the plans
for a group of SQL statements. For these situations, you can set up a
trace for the statements you want to examine and then use the second
utility, TKPROF, to give you the results of the trace in a more
readable format in a separate file.

You must use the EXPLAIN keyword when you start TKPROF, as this will
instruct the utility to execute an EXPLAIN PLAN statement for each
SQL statement in the trace file. You can also specify how the results
delivered by TKPROF are sorted. For instance, you can have the SQL
statements sorted on the basis of the physical I/Os they used; the
elapsed time spent on parsing, executing, or fetching the rows; or
the total number of rows affected.

The TKPROF utility uses a trace file as its raw material. Trace files
are created for individual sessions. You can start collecting a trace
file either by running the target application with a switch (if
it's written with an Oracle product such as
Developer) or by explicitly turning it on with an EXEC SQL call or an
ALTER SESSION SQL statement in an application written with a 3GL. The
trace process, as you can probably guess, can significantly affect
the performance of an application, so you should turn it on only when
you have some specific diagnostic work to do.

You can also view the execution plan through Enterprise Manager for
the SQL statements that use the most resources.

Tuning your SQL
statements isn't a trivial task, but with the
EXPLAIN PLAN and TKPROF utilities you can get to the bottom of the
decisions made by the cost-based optimizer. It takes a bit of work to
understand exactly how to read an execution plan, but
it's better to have access to this type of
information than not. In large-scale system-development projects,
it's quite common for developers to submit EXPLAIN
PLANs for the SQL they're writing to a DBA as a
formal step toward completing a form or report. While time-consuming,
this is the best way to ensure that your SQL is tuned before going
into production.