23
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 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. However, this plan 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. Changes to both the optimizer and EXPLAIN PLAN output will be documented as they arise.

The row source count values appearing in EXPLAIN PLAN output 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).

See also: Chapter 24, "The SQL Trace Facility and TKPROF"
Chapter 25, "Using Oracle Trace"

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 depends 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:

Table 23-1 PLAN_TABLE Columns

Column	Description
STATEMENT_ID	The value of the optional 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 who 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 Table 23-2 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 23-2 describes the values that may appear in the OTHER_TAG column.

Table 23-2 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 produced by the EXPLAIN PLAN command and its meaning within an execution plan.

Table 23-3 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 be used only if there are some nonnegated predicates yielding a bitmap from which the subtraction can take place. An example appears in "Bitmap Indexes and EXPLAIN PLAN" on page 23-10.
	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.

Table 23-4 OPERATION and OPTION Values Produced by EXPLAIN PLAN (Continued)

OPERATION	OPTION	Description
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.
HASH JOIN (These are join operations.)		An operation that joins two sets of rows, and returns the result.
	ANTI	A hash anti-join.
	SEMI	A hash semi-join.
INDEX (These operations are access methods.)	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	CONCATENATED	Iterates over the operation below it, for each value in the IN list predicate.
INTERSECTION		An operation that accepts two sets of rows and returns the intersection of the sets, eliminating duplicates.
MERGE JOIN (These are join operations.)		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.
	ANTI	A merge anti-join.
	SEMI	A merge semi-join.
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.

Table 23-5 OPERATION and OPTION Values Produced by EXPLAIN PLAN (Continued)

OPERATION	OPTION	Description
NESTED LOOPS (These are join operations.)		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 (These are join operations.)	OUTER	A nested loops operation to perform an outer join statement.
PARTITION	CONCATENATED	Iterates over the operation below it, for each partition in the range given by the PARTITION_START and PARTITION_STOP columns.
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.
	ORDER BY	An operation that sorts a set of rows for a query with an ORDER BY clause.
TABLE ACCESS (These operations are access methods.)	FULL	A retrieval of all rows from a table.
	CLUSTER	A retrieval of rows from a table based on a value of an indexed cluster key.
	HASH	Retrieval of rows from table based on hash cluster key value.
	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.

Note: Access methods and join operations are discussed in Oracle8 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

In this example, the predicate C1=2 yields a bitmap from which a subtraction can take place. 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.

INLIST ITERATOR and EXPLAIN PLAN

An INLIST ITERATOR operation appears in the EXPLAIN PLAN output if an index implements an IN list 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 IN list predicate.

For partitioned tables and indexes, the three possible types of IN list columns are described in the following sections.

Index Column

If the IN list column EMPNO is an index column but not a partition column, then the plan is as follows (the IN list 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 IN list 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 operation 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 operation 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

Note: The output of the EXPLAIN PLAN command reflects the behavior of the Oracle optimizer. As the optimizer evolves between releases of the Oracle server, output from the EXPLAIN PLAN command is also likely to evolve.

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 23-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. You can avoid this limitation by putting appropriate type conversions in the SQL statement.

23The EXPLAIN PLAN Command