Databases Reference
In-Depth Information
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. You can also view an execution
plan for a single SQL statement with SQL Developer or in the SQL Workshop area of
Application Express, which is discussed in
Chapter 15
.
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. At other times, you might also use Oracle's SQL Trace facility to generate
a file containing the SQL generated when using TKPROF in tuning applications.
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
