21
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 Oracle8 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).

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:

Run the SQL script UTLXPLAN.SQL to create a sample output table called PLAN_TABLE in your schema. The exact name and location of this script may vary depending on your operating system. PLAN_TABLE is the default table into which the EXPLAIN PLAN statement inserts rows describing execution plans.
Issue a CREATE TABLE statement to create an output table with any name you choose. When you issue an EXPLAIN PLAN statement you can direct its output to this table.

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.
PARTITION_START	The start partition of a range of accessed partitions.
PARTITION_STOP	The stop partition of a range of accessed partitions.
PARTITION_ID	The step that has computed the pair of values of the PARTITION_START and PARTITION_STOP columns.
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.

Table 21-1 describes the values which may appear in the OTHER_TAG column.

Table 21-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

Table 21-2 lists each combination of OPERATION and OPTION values produced by the EXPLAIN PLAN command and its meaning within an execution plan.

Table 21-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.
BITMAP	CONVERSION	TO ROWIDS converts the bitmap representation to actual rowids that can be used to access the table. FROM ROWIDS converts the rowids to a bitmap representation. COUNT returns the number of rowids if the actual values are not needed.
	INDEX	SINGLE VALUE looks up the bitmap for a single key value in the index. RANGE SCAN retrieves bitmaps for a key value range. FULL SCAN: A bitmap index full scan is performed if there is no start or stop key.
	MERGE	Merges several bitmaps resulting from a range scan into one bitmap
	MINUS	Subtracts the bits of one bitmap from another. This row source is used for negated predicates and can only be used if there are some non-negated predicates yielding a bitmap from which the subtraction can take place. In the example above, such a bitmap results from the predicate c1 = 2. From this bitmap, the bits in the bitmap for c2 = 6 are subtracted. Also, the bits in the bitmap for c2 IS NULL are subtracted, explaining why there are two MINUS row sources in the plan. The NULL subtraction is necessary for semantic correctness unless the column has a NOT NULL constraint.
	OR	computes the bitwise OR of two bitmaps
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.
COUNT	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.
INLIST ITERATOR	CONCATEN-ATED	Iterates over the operation below it, for each value in the inlist predicate.
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.
MERGE JOIN⁺	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.
NESTED LOOPS⁺	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 Oracle8 Server Concepts.

Bitmap Indexes and EXPLAIN PLAN

Index row sources appear in the EXPLAIN PLAN output with the word BITMAP indicating the type. Consider the following sample query and plan, in which the TO ROWIDS option is used to generate the rowids that are necessary for table access.

EXPLAIN PLAN FOR

SELECT * FROM T

WHERE

C1 = 2 AND C2 <> 6 

OR

C3 BETWEEN 10 AND 20;

SELECT STATEMENT 

  TABLE ACCESS  T  BY ROWID

    BITMAP CONVERSION TO ROWIDS

      BITMAP OR

        BITMAP MINUS

          BITMAP MINUS

            BITMAP INDEX  C1_IND  SINGLE VALUE

            BITMAP INDEX  C2_IND  SINGLE VALUE

          BITMAP INDEX  C2_IND  SINGLE VALUE

        BITMAP MERGE

          BITMAP INDEX  C3_IND  RANGE SCAN

Inlist Iterator and EXPLAIN PLAN

An INLIST ITERATOR operation appears in the EXPLAIN PLAN output if an index implements an inlist predicate. For example, for the query

SELECT * FROM EMP WHERE empno IN (7876, 7900, 7902);

the EXPLAIN PLAN output is as follows:

OPERATION          OPTIONS           OBJECT_NAME

----------------   ---------------   -------------- 

SELECT STATEMENT

INLIST ITERATOR    CONCATENATED

TABLE ACCESS       BY ROWID          EMP

INDEX              RANGE SCAN        EMP_EMPNO

The inlist iterator operation iterates over the operation below it for each value in the inlist predicate.

For partitioned tables and indexes, there are three possibilities, described in the following sections.

Index Column

If the inlist column empno is an index column but not a partition column, then the plan is as follows (the inlist operator appears above the table operation but below the partition operation):

OPERATION         OPTIONS        OBJECT_NAME   PARTITION_START   PARTITION_STOP

----------------  ------------   -----------   ---------------   --------------

SELECT STATEMENT

PARTITION         CONCATENATED                 KEY(INLIST)       KEY(INLIST)

INLIST ITERATOR   CONCATENATED

TABLE ACCESS      BY ROWID       EMP           KEY(INLIST)       KEY(INLIST)

INDEX             RANGE SCAN     EMP_EMPNO     KEY(INLIST)       KEY(INLIST)

The KEY(INLIST) designation for the partition start and stop keys specifies that an inlist predicate appears on the index start/stop keys.

Index and Partition Column

If empno is an indexed and a partition column, then the plan contains an inlist iterator above the partition operation:

OPERATION         OPTIONS        OBJECT_NAME   PARTITION_START   PARTITION_STOP

----------------  ------------   -----------   ---------------   --------------

SELECT STATEMENT

INLIST ITERATOR   CONCATENATED

PARTITION         CONCATENATED                 KEY(INLIST)       KEY(INLIST)

TABLE ACCESS      BY ROWID       EMP           KEY(INLIST)       KEY(INLIST)

INDEX             RANGE SCAN     EMP_EMPNO     KEY(INLIST)       KEY(INLIST)

Partition Column

If empno is a partition column and there are no indexes, then no inlist iterator is allocated:

OPERATION         OPTIONS        OBJECT_NAME   PARTITION_START   PARTITION_STOP

----------------  ------------   -----------   ---------------   --------------

SELECT STATEMENT

PARTITION         CONCATENATED                 KEY(INLIST)       KEY(INLIST)

TABLE ACCESS      BY ROWID       EMP           KEY(INLIST)       KEY(INLIST)

INDEX             RANGE SCAN     EMP_EMPNO     KEY(INLIST)       KEY(INLIST)

If emp_empno is a bitmap index, then the plan is as follows:

OPERATION          OPTIONS           OBJECT_NAME

----------------   ---------------   -------------- 

SELECT STATEMENT

INLIST ITERATOR    CONCATENATED

TABLE ACCESS       BY INDEX ROWID    EMP

BITMAP CONVERSION  TO ROWIDS

BITMAP INDEX       SINGLE VALUE      EMP_EMPNO

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.

Note: A CONNECT BY does not preserve ordering. To have rows come out in the correct order in this example, you must either truncate the table first, or else create a view and select from the view. For example:

CREATE VIEW test AS

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 = 'TST'

CONNECT BY prior id = parent_id and statement_id = 'TST';

SELECT * FROM foo ORDER BY id, parent_id;

This yields results as follows:

ID  PAR Query Plan

--- --- --------------------------------------------------

  0     Select Statement   Cost = 69602

  1   0   Nested Loops

  2   1     Nested Loops

  3   2       Merge Join

  4   3         Sort Join

  5   4           Table Access Full T3

  6   3         Sort Join

  7   6           Table Access Full T4

  8   2       Index Unique Scan T2

  9   1     Table Access Full T1

10 rows selected.

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 21-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.

EXPLAIN PLAN Restrictions

EXPLAIN PLAN is not supported for statements that perform implicit type conversion of date bind variables. With bind variables in general the EXPLAIN PLAN output may not represent the real execution plan. From the text of a SQL statement, TKPROF cannot determine the type of the bind variables. It assumes that the type is CHARACTER, and gives an error message if this is not the case. This limitation can be worked around by putting appropriate type conversions in the SQL statement.

21 The EXPLAIN PLAN Command