Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

12.2 Using EXPLAIN PLAN

EXPLAIN PLAN
is a SQL statement that causes Oracle to
report the execution plan it would choose for any SELECT, INSERT,
UPDATE, DELETE, or MERGE statement. An execution
plans refers to the approach Oracle will take to retrieve
the necessary data for a statement. One example of a plan would be to
use an index to find the required rows. Another example of an
execution plan would be to sequentially read all rows in the table.
If you have a poorly performing SQL statement, you can use EXPLAIN
PLAN to find out how Oracle is processing it. With that information,
you may be able to take some corrective action to improve
performance.

When you use EXPLAIN PLAN, Oracle doesn't display
its execution strategy on the screen; instead, it inserts rows into a
table. This table is referred to as the plan
table , and you must query it properly to see
the results. The plan table must exist; if you've
never used EXPLAIN PLAN before, you may need to create the plan table
first.

Oracle occasionally adds columns to the plan table. If you have a plan table created using a previous version of Oracle, you may want to drop and re-create it to be sure you have the most recent version.

12.2.1 Creating the Plan Table

If you're running Oracle Database
10 g , the good news is you don't
need to create a plan table. Instead, you can let the EXPLAIN PLAN
statement create the table for you. If no plan table exists, EXPLAIN
PLAN will create a global temporary plan table. The EXPLAIN PLAN
results in such a table will remain for the duration of your session.
The temporary table definition will be permanent.

If you're not running Oracle Database
10 g , or if you prefer working with a plan table
that will hold results across more than one session until you delete
them, you'll need to create a permanent plan table.
For that, Oracle provides a script named
utlxplan.sql, which resides in the
$ORACLE_HOME/rdbms/admin directory for your
database. You can run the script from SQL*Plus like this:

SQL>
@$ORACLE_HOME/rdbms/admin/utlxplan
Table created.

Windows users, refer to the Oracle home directory as follows: @%ORACLE_HOME%/rdbms/admin/utlxplan In particular, note the bracketing of ORACLE_HOME by percent-signs (%).

The columns in the plan table vary from release to release, as Oracle
has tended to add columns over the years. Here is what the Oracle
Database 10 g plan table looks like:

SQL>
DESCRIBE plan_table
Name Null? Type
----------------------------------------- -------- ----------------
STATEMENT_ID VARCHAR2(30)
PLAN_ID NUMBER
TIMESTAMP DATE
REMARKS VARCHAR2(4000)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_ALIAS VARCHAR2(65)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
DEPTH NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
DISTRIBUTION VARCHAR2(30)
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER(38)
QBLOCK_NAME VARCHAR2(30) The name of the table doesn't have to be
plan_table, but that's the
default and it's usually easiest to leave it that
way. If for some reason you don't have access to the
utlxplan.sql script, you can create the table
manually. Be sure that the column names and datatypes match those
shown here.

12.2.2 Explaining a Query

Once
you have a
plan table, getting Oracle to tell you the execution plan for any
given query is an easy task. You need to prepend the EXPLAIN PLAN
statement to the front of your query. The syntax for EXPLAIN PLAN
looks like this:

EXPLAIN PLAN
[SET STATEMENT_ID = '
statement_id
']
[INTO
table_name
]
FOR
statement
;

in which:

statement_id
Can be anything you like, and is stored in the
STATEMENT_ID field of all plan table records
related to the query you are explaining. It defaults to null.

table_name
Is the name of the plan table, and defaults
to PLAN_TABLE. You need to supply
this value if you have created your plan table with some name other
than the default.

statement
Is the DML statement to be
"explained." This can be an INSERT,
UPDATE, DELETE, SELECT, or MERGE statement, but it must not reference
any data dictionary views or dynamic performance tables.

Consider the following query, which returns the total number of hours
worked by each employee on each project:

SELECT employee_name, project_name, sum(hours_logged)
FROM employee, project, project_hours
WHERE employee.employee_id = project_hours.employee_id
AND project.project_id = project_hours.project_id
GROUP BY employee_name, project_name;

This query can be explained using the two statements shown in Example 12-2.

Example 12-2. Explaining a query

DELETE FROM plan_table WHERE statement_id = 'HOURS_BY_PROJECT';
EXPLAIN PLAN
SET STATEMENT_ID = 'HOURS_BY_PROJECT'
FOR
SELECT employee_name, project_name, sum(hours_logged)
FROM employee, project, project_hours
WHERE employee.employee_id = project_hours.employee_id
AND project.project_id = project_hours.project_id
GROUP BY employee_name, project_name;

When you execute this EXPLAIN PLAN statement, you
won't see any output because Oracle stores the query
plan in the plan table. Retrieving and interpreting the results is
your next task.

You should include a DELETE statement prior to the EXPLAIN PLAN statement. When you explain a statement, Oracle doesn't clear the plan table of any previous rows with the same statement ID. If rows with the same statement ID exist from previous executions of EXPLAIN PLAN, you will get strange results. If you're the only person using the plan table, you can save yourself some typing by omitting the WHERE clause in the DELETE statement, thereby deleting all the records in the plan table. In that case, you don't need to bother with the SET STATEMENT_ID clause.

12.2.3 Interpreting the Results

Having
issued
an EXPLAIN PLAN, you retrieve and view the results by querying the
plan table. The statement ID is the key to doing this. The plan table
can contain execution plans for any number of queries. The rows for
each query contain the statement ID you specified in your EXPLAIN
PLAN statement, so you must use this same ID when querying the plan
table to select the plan you are interested in seeing.

12.2.3.1 Using DBMS_XPLAN to display an execution plan

Beginning in Oracle9 i Database Release 2, you
can display an execution plan with a call to DBMS_XPLAN.DISPLAY,
which is a table function. Example 12-3 shows how to
use that function to display the plan generated in Example 12-2. The SET LINESIZE 132 command is there because
the results require a bit more than 80 characters per line. Figure 12-1 shows the resulting plan output.

Example 12-3. Invoking DBMS_XPLAN.DISPLAY to show an execution plan

SET LINESIZE 132
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY(
'PLAN_TABLE','HOURS_BY_PROJECT','TYPICAL')
);

This is by far the easiest way to display execution plan details. It
would be nice if the TYPICAL display fit within 80 columns, but it
doesn't. You may wish to SET LINESIZE 132 prior to
displaying a plan.

The three arguments to the DBMS_XPLAN.DISPLAY function are:

table_name
Don't forget to specify your plan
table's name as uppercase unless you have a mixed-
or lowercase plan table name.

statement_ID
This is the ID you gave in your EXPLAIN PLAN statement. Use NULL if
you did not specify a statement ID.

format
The format keyword describes how much detail you
wish to see. It will ordinarily be one of the following, in
increasing order of detail: BASIC, TYPICAL, ALL. TYPICAL is the
default. You may use SERIAL in cases in which you want TYPICAL output
but without any information about parallel operations.

Oracle breaks query execution down into a series of nested steps,
each of which feeds data to a parent step. The ultimate parent is the
query itself, the output of which is returned to the application. You
can see this nesting reflected in Figure 12-1.

When using DBMS_XPLAN to display execution plans, and when leaving statement_id NULL, I haven't had to worry about deleting rows from the plan table prior to re-explaining a plan. See my note at the end of the preceding section.

12.2.3.2 Using a SELECT statement to display an execution plan

If you're running Oracle9i Database Release 1 or
earlier, you'll need to use the traditional way to
look at an execution plan, which is to display it using a
hierarchical query. Example 12-4 shows a typical
query used to display plan output.

Example 12-4. A query to display an execution plan

COLUMN id FORMAT 9999
COLUMN parent_id FORMAT 9999 HEADING "PID"
COLUMN "Query Plan" FORMAT A50
UNDEFINE s_statement_id
SELECT id, parent_id,
LPAD(' ', 2*(level-1)) || operation || ' ' || options
|| ' ' || object_name || ' ' ||
DECODE(id, 0, 'Cost = ' || position) "Query Plan"
FROM plan_table
START WITH id = 0 AND statement_id = '&&s_statement_id'
CONNECT BY prior id = parent_id AND statement_id = '&&s_statement_id';

The result of this query will be a report showing the steps in the
execution plan, with each child step indented underneath its parent:

SQL>
@ex12-3
Enter value for s_statement_id:
HOURS_BY_PROJECT
old 6: START WITH id = 0 AND statement_id = '&&s_statement_id'
new 6: START WITH id = 0 AND statement_id = 'HOURS_BY_PROJECT'
old 7: CONNECT BY prior id = parent_id AND statement_id = '&&s_statement_id'
new 7: CONNECT BY prior id = parent_id AND statement_id = 'HOURS_BY_PROJECT'
ID PID Query Plan
----- ----- --------------------------------------------------
0 SELECT STATEMENT Cost = 11
1 0 SORT GROUP BY
2 1 HASH JOIN
3 2 TABLE ACCESS FULL EMPLOYEE
4 2 MERGE JOIN
5 4 TABLE ACCESS BY INDEX ROWID PROJECT
6 5 INDEX FULL SCAN PROJECT_PK
7 4 SORT JOIN
8 7 TABLE ACCESS FULL PROJECT_HOURS Depending on your needs, you can include additional columns besides
those shown here. Table 12-1 describes each of the
plan table columns. Be aware that many columns, especially those
involving cost, are not filled in by the rule-based optimizer.

You don't need to write your own plan table script if you don't want to. Oracle distributes two scripts in the $ORACLE_HOME/rdbms/admin directory: utlxplp.sql to explain parrallel plans, and utlxpls.sql to explain serial plans.

12.2.3.3 Making sense of the results

The key to interpreting an execution plan is to understand that the
display is hierarchical. A step may consist of one or more child
steps, and these child steps are shown indented underneath their
parent. Executing any given step involves executing all its children,
so to understand the plan, you pretty much have to work your way out
from the innermost step. For each step in the plan,
you'll at least want to look at the operation name,
at any options that apply, and at the object of the operation. You
may also want to look at the optimizer's cost
estimate. All of these are shown in Figure 12-1.

If you are using the rule-based optimizer, the cost will be null. Oracle will use the rule-based optimizer, even if your database is set to CHOOSE the cost-based optimizer, if you haven't used SQL's ANALYZE TABLE statement to gather statistics for any of the tables involved in the query.

If you're using the cost-based optimizer, it will
compute an estimated cost for each operation and for the statement as
a whole. In Figure 12-1, the estimate cost for the
SELECT STATEMENT operation is 11. This cost means nothing by itself,
but the optimizer uses it to compare alternative plans resulting from
the query. If you add a hint to the query that changes the cost
estimate from 11 to 22, you've probably made your
query's performance worse. If you make a change that
drives your query's cost from 11 to 5,
you've likely made an improvement.
Don't fall into the trap, though, of comparing cost
estimates from two, unrelated queries.

For Oracle to compute an accurate cost, you must have up-to-date statistics on the tables involved in the query. Use the ANALYZE TABLE statement to gather these statistics. If your statistics are old, the optimizer may come up with an execution plan that won't be efficient for the data you have now.

Table 12-2 provides a brief description of the
various operations, together with their options, that you may see
when querying the plan table. For more detailed information about any
of these operations, refer to the Oracle Database
Performance Tuning Guide (Oracle Corporation) .