Oracle7 Tuning, release 7.3.3 Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

The EXPLAIN PLAN Command


This chapter shows how to use the SQL command EXPLAIN PLAN.
It covers the following topics:

See Also: For the syntax of the EXPLAIN PLAN command, see the Oracle7 Server SQL Reference.

Introduction

The EXPLAIN PLAN command displays the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations that Oracle performs to execute the statement. By examining the execution plan, you can see exactly how Oracle executes your SQL statement.

EXPLAIN PLAN results alone cannot tell you which statements will perform well, and which badly. For example, just because EXPLAIN PLAN indicates that a statement will use an index does not mean that the statement will run quickly. The index might be very inefficient! Use EXPLAIN PLAN to determine the access plan, and to test modifications to improve the performance.

It is not necessarily useful to subjectively evaluate the plan for a statement, and decide to tune it based only on the execution plan. Instead, you should examine the statement's actual resource consumption. For best results, use the Oracle Trace or SQL trace facility and TKPROF to examine performance information on individual SQL statements.

Attention: EXPLAIN PLAN tells you the execution plan the optimizer would choose if it were to produce an execution plan for a SQL statement at the current time, with the current set of initialization and session parameters. But this is not necessarily the same as the plan that was used at the time the given statement was actually executed. The optimizer bases its analysis on many pieces of data--some of which may have changed! Furthermore, because the behavior of the optimizer is likely to evolve between releases of the Oracle Server, output from the EXPLAIN PLAN command will also evolve. Such changes will be documented as they arise.

The row source count values appearing in EXPLAIN PLAN identify the number of rows that have been processed by each step in the plan. This can help you to identify where the inefficiency in the query lies (that is, the row source with an access plan that is performing inefficient operations).k

Creating the Output Table

Before you can issue an EXPLAIN PLAN statement, you must create a table to hold its output. Use one of the following approaches:

Any table used to store the output of the EXPLAIN PLAN command must have the same column names and datatypes as the PLAN_TABLE:

CREATE TABLE plan_table 
(statement_id VARCHAR2(30),
timestamp DATE,
remarks VARCHAR2(80),
operation VARCHAR2(30),
options VARCHAR2(30),
object_node VARCHAR2(128),
object_owner VARCHAR2(30),
object_name VARCHAR2(30),
object_instance NUMERIC,
object_type VARCHAR2(30),
optimizer VARCHAR2(255),
search_columns NUMERIC,
id NUMERIC,
parent_id NUMERIC,
position NUMERIC,
cost NUMERIC,
cardinality NUMERIC,
bytes NUMERIC,
other_tag VARCHAR2(255)
other LONG);

Output Table Columns

The PLAN_TABLE used by the EXPLAIN PLAN command contains the following columns:

STATEMENT_ID

 

The value of the option STATEMENT_ID parameter specified in the EXPLAIN PLAN statement.

 

TIMESTAMP

 

The date and time when the EXPLAIN PLAN statement was issued.

 

REMARKS

 

Any comment (of up to 80 bytes) you wish to associate with each step of the explained plan. If you need to add or change a remark on any row of the PLAN_TABLE, use the UPDATE statement to modify the rows of the PLAN_TABLE.

 

OPERATION

 

The name of the internal operation performed in this step. In the first row generated for a statement, the column contains one of the following values:

'DELETE STATEMENT'
'INSERT STATEMENT'
'SELECT STATEMENT'
'UPDATE STATEMENT'

 

OPTIONS

 

A variation on the operation described in the OPERATION column.

 

OBJECT_NODE

 

The name of the database link used to reference the object (a table name or view name). For local queries using the parallel query option, this column describes the order in which output from operations is consumed.

 

OBJECT_OWNER

 

The name of the user that owns the schema containing the table or index.

 

OBJECT_NAME

 

The name of the table or index.

 

OBJECT_INSTANCE

 

A number corresponding to the ordinal position of the object as it appears in the original statement. The numbering proceeds from left to right, outer to inner with respect to the original statement text. Note that view expansion will result in unpredictable numbers.

 

OBJECT_TYPE

 

A modifier that provides descriptive information about the object; for example, NON-UNIQUE for indexes.

 

OPTIMIZER

 

The current mode of the optimizer.

 

SEARCH_COLUMNS

 

Not currently used.

 

ID

 

A number assigned to each step in the execution plan.

 

PARENT_ID

 

The ID of the next execution step that operates on the output of the ID step.

 

POSITION

 

The order of processing for steps that all have the same PARENT_ID.

 

OTHER

 

Other information that is specific to the execution step that a user may find useful.

 

OTHER_TAG

 

Describes the contents of the OTHER column. See below for more information on the possible values for this column.

 

COST

 

The cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement, it is merely a weighted value used to compare costs of execution plans.

 

CARDINALITY

 

The estimate by the cost-based approach of the number of rows accessed by the operation.

 

BYTES

 

The estimate by the cost-based approach of the number of bytes accessed by the operation.

 

The following table describes the values which may appear in the OTHER_TAG column.

Table 20-1: Values of OTHER_TAG Column of the PLAN_TABLE
OTHER_TAG Text   Interpretation  

(blank)

 

serial execution

 

serial_from_remote

 

serial execution at a remote site

 

serial_to_parallel

 

serial execution, output of step is partitioned or broadcast to parallel query servers

 

parallel_to_parallel

 

parallel execution, output of step is repartitioned to second set of parallel query servers

 

parallel_to_serial

 

parallel execution, output of step is returned to serial "query coordinator" process

 

parallel_combined_with_parent

 

parallel execution, output of step goes to next step in same parallel process. No interprocess communication to parent

 

parallel_combined_with_child

 

parallel execution, input of step comes from prior step in same parallel process. No interprocess communication from child

 

The following table lists each combination of OPERATION and OPTION values produced by the EXPLAIN PLAN command and its meaning within an execution plan.

Table 20-2: OPERATION and OPTION Values Produced by EXPLAIN PLAN
OPERATION   OPTION   Description  

AND-EQUAL

 

 

An operation that accepts multiple sets of ROWIDS and returns the intersection of the sets, eliminating duplicates. This operation is used for the single-column indexes access path.

 

CONNECT BY

 

 

A retrieval of rows in a hierarchical order for a query containing a CONNECT BY clause.

 

CONCATENATION

 

 

An operation that accepts multiple sets of rows and returns the union-all of the sets.

 

COUNT

 

 

An operation that counts the number of rows selected from a table.

 

STOPKEY

 

A count operation where the number of rows returned is limited by the ROWNUM expression in the WHERE clause.

 

FILTER

 

 

An operation that accepts a set of rows, eliminates some of them, and returns the rest.

 

FIRST ROW

 

 

A retrieval on only the first row selected by a query.

 

FOR UPDATE

 

 

An operation that retrieves and locks the rows selected by a query containing a FOR UPDATE clause.

 

INDEX*

 

UNIQUE SCAN

 

A retrieval of a single ROWID from an index.

 

RANGE SCAN

 

A retrieval of one or more ROWIDs from an index. Indexed values are scanned in ascending order.

 

RANGE SCAN DESCENDING

 

A retrieval of one or more ROWIDs from an index. Indexed values are scanned in descending order.

 

INTERSECTION

 

 

An operation that accepts two sets of rows and returns the intersection of the sets, eliminating duplicates.

 

MERGE JOIN+

 

 

An operation that accepts two sets of rows, each sorted by a specific value, combines each row from one set with the matching rows from the other, and returns the result.

 

OUTER

 

A merge join operation to perform an outer join statement.

 

CONNECT BY

 

 

A retrieval of rows in hierarchical order for a query containing a CONNECT BY clause.

 

MINUS

 

 

An operation that accepts two sets of rows and returns rows that appear in the first set but not in the second, eliminating duplicates.

 

NESTED LOOPS+

 

 

An operation that accepts two sets of rows, an outer set and an inner set. Oracle compares each row of the outer set with each row of the inner set and returns those rows that satisfy a condition.

 

OUTER

 

A nested loops operation to perform an outer join statement.

 

PROJECTION

 

 

An internal operation.

 

REMOTE

 

 

A retrieval of data from a remote database.

 

SEQUENCE

 

 

An operation involving accessing values of a sequence.

 

SORT

 

AGGREGATE

 

A retrieval of a single row that is the result of applying a group function to a group of selected rows.

 

UNIQUE

 

An operation that sorts a set of rows to eliminate duplicates.

 

GROUP BY

 

An operation that sorts a set of rows into groups for a query with a GROUP BY clause.

 

JOIN

 

An operation that sorts a set of rows before a merge-join operation.

 

ORDER BY

 

An operation that sorts a set of rows for a query with an ORDER BY clause.

 

TABLE ACCESS*

 

FULL

 

A retrieval of all rows from a table.

 

CLUSTER

 

A retrieval of rows from a table based on a value of the key of an indexed cluster.

 

HASH

 

A retrieval of rows from a table based on a value of the key of hash cluster.

 

BY ROWID

 

A retrieval of a row from a table based on its ROWID.

 

UNION

 

 

An operation that accepts two sets of rows and returns the union of the sets, eliminating duplicates.

 

VIEW

 

 

An operation that performs a view's query and then returns the resulting rows to another operation.

 

* These operations are access methods.
+ These operations are join operations.

Both access methods and join operations are discussed in Appendix A, "Optimizer Concepts".

Formatting EXPLAIN PLAN Output

This section shows options for formatting EXPLAIN PLAN output

How to Run EXPLAIN PLAN

The following example shows a SQL statement and its corresponding execution plan generated by EXPLAIN PLAN. The sample query retrieves names and related information for employees whose salary is not within any range of the SALGRADE table:

SELECT ename, job, sal, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND NOT EXISTS
(SELECT *
FROM salgrade
WHERE emp.sal BETWEEN losal AND hisal);

This EXPLAIN PLAN statement generates an execution plan and places the output in PLAN_TABLE:

EXPLAIN PLAN
SET STATEMENT_ID = 'Emp_Sal'
FOR SELECT ename, job, sal, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND NOT EXISTS
(SELECT *
FROM salgrade
WHERE emp.sal BETWEEN losal AND hisal);

Selecting PLAN_TABLE Output in Table Format

This SELECT statement generates the following output:

SELECT operation, options, object_name, id, parent_id, position
FROM plan_table
WHERE statement_id = 'Emp_Sal'
ORDER BY id; OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION COST CARDINALITY BYTES OTHER_TAG OPTIMIZER
--------------------------------------------------------------------------------------------------
SELECT STATEMENT 0 2 2 1 62 CHOOSE
FILTER 1 0 1
NESTED LOOPS 2 1 1 2 1 62
TABLE ACCESS FULL EMP 3 2 1 1 1 40 ANALYZED
TABLE ACCESS FULL DEPT 4 2 2 4 88 ANALYZED
TABLE ACCESS FULL SALGRADE 5 1 2 1 1 13 ANALYZED

The ORDER BY clause returns the steps of the execution plan sequentially by ID value. However, Oracle does not perform the steps in this order. PARENT_ID receives information from ID: yet more than one ID step fed into PARENT_ID. For example, step 2, a merge join, and step 7, a table access, both fed into step 1. A nested, visual representation of the processing sequence is shown in the next section.

The value of the POSITION column for the first row of output indicates the optimizer's estimated cost of executing the statement with this plan to be 5. For the other rows, it indicates the position relative to the other children of the same parent.

Selecting PLAN_TABLE Output in Nested Format

This type of SELECT statement generates a nested representation of the output that more closely depicts the processing order used for the SQL statement.

SELECT 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 = 'Emp_Sal'
CONNECT BY PRIOR id = parent_id AND statement_id ='Emp_Sal';

Query Plan
------------------------------
SELECT STATEMENT Cost = 5
FILTER
NESTED LOOPS
TABLE ACCESS FULL EMP
TABLE ACCESS FULL DEPT
TABLE ACCESS FULL SALGRADE

The order resembles a tree structure, illustrated in the following figure.




Figure 20-1: Tree Structure of an Execution Plan

The tree structure illustrates how operations that occur during the execution of a SQL statement feed one another. Each step in the execution plan is assigned a number (representing the ID column of the PLAN_TABLE) and is depicted by a "node". The result of each node's operation passes to its parent node, which uses it as input.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index