13
Using EXPLAIN PLAN

This chapter introduces execution plans, the SQL statement EXPLAIN PLAN, and describes how to interpret its output. This chapter also explains plan stability features and the use of stored outlines to preserve your tuning investment for particular SQL statements. This chapter also provides procedures for managing outlines to control application performance characteristics. This chapter covers the following topics:

Introduction to EXPLAIN PLAN

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to execute the statement. The components of execution plans include:

An ordering of the tables referenced by the statement
An access method for each table mentioned in the statement
A join method for tables affected by join operations in the statement

EXPLAIN PLAN output shows how Oracle executes SQL statements. EXPLAIN PLAN results alone, however, cannot differentiate between well-tuned statements and those that perform poorly. For example, if EXPLAIN PLAN output shows that a statement uses an index, this does not mean the statement runs efficiently. Sometimes using indexes can be extremely inefficient. It is thus best to use EXPLAIN PLAN to determine an access plan and later prove it is the optimal plan through testing.

When evaluating a plan, always examine the statement's actual resource consumption. For best results, use the Oracle Trace or SQL trace facility and TKPROF to examine individual SQL statement performance.

See Also:
Chapter 14, "The SQL Trace Facility and TKPROF" and Chapter 15, "Using Oracle Trace".

Creating the Output Table

Before issuing an EXPLAIN PLAN statement, 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 statement 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);

Displaying PLAN_TABLE Output

Display the most recent plan table output using the scripts:

UTLPLS.SQL - To show plan table output for serial processing.
UTLXPLP.SQL - To show plan table output with parallel execution columns.

The row source count values in EXPLAIN PLAN output identify the number of rows processed by each step in the plan. This helps you identify inefficiencies in the query, for example, the row source with an access plan that is performing inefficient operations.

Output Table Columns

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

Table 13-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 See Table 13-4 for more information on values for this column.
OPTIONS	A variation on the operation described in the OPERATION column. See Table 13-4 for more information on values for this column.
OBJECT_NODE	The name of the database link used to reference the object (a table name or view name). For local queries using parallel execution, 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. 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 13-2 for more information on the possible values for this column.
DISTRIBUTION	Stores the method used to distribute rows from "producer" query servers to "consumer" query servers. For more information about consumer and producer query servers, please see Oracle8i Concepts.
Pstart	The start partition of a range of accessed partitions. It can take one of the following values: n indicates that the start partition has been identified by the SQL compiler and its partition number is given by n. KEY indicates that the start partition will be identified at execution time from partitioning key values. ROW LOCATION indicates that the start partition (same as the stop partition) will be computed at execution time from the location of each record being retrieved. The record location is obtained by a user or from a global index. INVALID indicates that the range of accessed partitions is empty.
Pstop	The stop partition of a range of accessed partitions. It can take one of the following values: n indicates that the stop partition has been identified by the SQL compiler and its partition number is given by n. KEY indicates that the stop partition will be identified at execution time from partitioning key values. ROW LOCATION indicates that the stop partition (same as the start partition) will be computed at execution time from the location of each record being retrieved. The record location is obtained by a user or from a global index. INVALID indicates that the range of accessed partitions is empty.
PID	The step that has computed the pair of values of the Pstart and Pstop 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 13-2 describes the values that may appear in the OTHER_TAG column.

Table 13-2 Values of OTHER_TAG Column of the PLAN_TABLE

OTHER_TAG Text (examples)	Meaning	Interpretation
blank		Serial execution.
serial_from_remote (S -> R)	Serial from remote	Serial execution at a remote site.
serial_to_parallel (S -> P)	Serial to parallel	Serial execution; output of step is partitioned or broadcast to parallel execution servers.
parallel_to_parallel (P - > P)	Parallel to parallel	Parallel execution; output of step is repartitioned to second set of parallel execution servers.
parallel_to_serial (P -> S)	Parallel to serial	Parallel execution; output of step is returned to serial "query coordinator" process.
parallel_combined_with_parent (PwP)	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 (PwC)	Parallel combined with child	Parallel execution; input of step comes from prior step in same parallel process. No interprocess communication from child.

Table 13-3 describes the values that can appear in the DISTRIBUTION column:

Table 13-3 Values of DISTRIBUTION Column of the PLAN_TABLE

DISTRIBUTION Text	Interpretation
PARTITION (ROWID)	Maps rows to query servers based on the partitioning of a table/index using the rowid of the row to UPDATE/DELETE.
PARTITION (KEY)	Maps rows to query servers based on the partitioning of a table/index using a set of columns. Used for partial partition-wise join, PARALLEL INSERT, CREATE TABLE AS SELECT of a partitioned table, and CREATE PARTITIONED GLOBAL INDEX.
HASH	Maps rows to query servers using a hash function on the join key. Used for PARALLEL JOIN or PARALLEL GROUP BY.
RANGE	Maps rows to query servers using ranges of the sort key. Used when the statement contains an ORDER BY clause.
ROUND-ROBIN	Randomly maps rows to query servers.
BROADCAST	Broadcasts the rows of the entire table to each query server. Used for a parallel join when one table is very small compared to the other.
QC (ORDER)	The query coordinator consumes the input in order, from the first to the last query server. Used when the statement contains an ORDER BY clause.
QC (RANDOM)	The query coordinator consumes the input randomly. Used when the statement does not have an ORDER BY clause.

Table 13-4 lists each combination of OPERATION and OPTION produced by the EXPLAIN PLAN statement and its meaning within an execution plan.

Table 13-4 OPERATION and OPTION Values Produced by EXPLAIN PLAN

OPERATION	OPTION	Description
AND-EQUAL		Operation accepting multiple sets of rowids, returning the intersection of the sets, eliminating duplicates. Used for the single-column indexes access path.
	CONVERSION	TO ROWIDS converts bitmap representations 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: Performs a full scan of a bitmap index if there is no start or stop key.
	MERGE	Merges several bitmaps resulting from a range scan into one bitmap.
	MINUS	Subtracts bits of one bitmap from another. Row source is used for negated predicates. Can be used only if there are nonnegated predicates yielding a bitmap from which the subtraction can take place. An example appears in "Bitmap Indexes and EXPLAIN PLAN".
	OR	Computes the bitwise OR of two bitmaps.
CONNECT BY		Retrieves rows in hierarchical order for a query containing a CONNECT BY clause.
CONCATENATION		Operation accepting multiple sets of rows returning the union-all of the sets.
COUNT		Operation counting the number of rows selected from a table.
COUNT	STOPKEY	Count operation where the number of rows returned is limited by the ROWNUM expression in the WHERE clause.
DOMAIN INDEX		Retrieval of one or more rowids from a domain index.
FILTER		Operation accepting a set of rows, eliminates some of them, and returns the rest.
FIRST ROW		Retrieval on only the first row selected by a query.
FOR UPDATE		Operation retrieving and locking the rows selected by a query containing a FOR UPDATE clause.
HASH JOIN (These are join operations.)		Operation joining two sets of rows and returning the result.
	ANTI	Hash anti-join.
	SEMI	Hash semi-join.
INDEX (These operations are access methods.)	UNIQUE SCAN	Retrieval of a single rowid from an index.
	RANGE SCAN	Retrieval of one or more rowids from an index. Indexed values are scanned in ascending order.
	RANGE SCAN DESCENDING	Retrieval of one or more rowids from an index. Indexed values are scanned in descending order.
INLIST ITERATOR		Iterates over the operation below it, for each value in the IN list predicate.
INTERSECTION		Operation accepting two sets of rows and returning the intersection of the sets, eliminating duplicates.
MERGE JOIN (These are join operations.)		Operation accepting two sets of rows, each sorted by a specific value, combining each row from one set with the matching rows from the other, and returning the result.
	OUTER	Merge join operation to perform an outer join statement.
	ANTI	Merge anti-join.
	SEMI	Merge semi-join.
CONNECT BY		Retrieval of rows in hierarchical order for a query containing a CONNECT BY clause.
MINUS		Operation accepting two sets of rows and returning rows appearing in the first set but not in the second, eliminating duplicates.
NESTED LOOPS (These are join operations.)		Operation accepting 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, returning rows that satisfy a condition.
NESTED LOOPS (These are join operations.)	OUTER	Nested loops operation to perform an outer join statement.
PARTITION	SINGLE	Access one partition.
	ITERATOR	Access many partitions (a subset).
	ALL	Access all partitions.
	INLIST	Similar to iterator but based on an inlist predicate.
	INVALID	Indicates that the partition set to be accessed is empty.
		Iterates over the operation below it, for each partition in the range given by the PARTITION_START and PARTITION_STOP columns. PARTITION describes partition boundaries applicable to a single partitioned object (table or index) or to a set of equi-partitioned objects (a partitioned table and its local indexes). The partition boundaries are provided by the values of pstart and pstop of the PARTITION. Refer to Table 13-1 for valid values of partition start/stop.
PROJECTION		Internal operation.
REMOTE		Retrieval of data from a remote database.
SEQUENCE		Operation involving accessing values of a sequence.
SORT	AGGREGATE	Retrieval of a single row that is the result of applying a group function to a group of selected rows.
	UNIQUE	Operation sorting a set of rows to eliminate duplicates.
	GROUP BY	Operation sorting a set of rows into groups for a query with a GROUP BY clause.
	JOIN	Operation sorting a set of rows before a merge-join.
	ORDER BY	Operation sorting a set of rows for a query with an ORDER BY clause.
TABLE ACCESS (These operations are access methods.)	FULL	Retrieval of all rows from a table.
	CLUSTER	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	Retrieval of a row from a table based on its rowid.
	BY USER ROWID	If the table rows are located using user-supplied rowids.
	BY INDEX ROWID	If the table is nonpartitioned and rows are located using index(es).
	BY GLOBAL INDEX ROWID	If the table is partitioned and rows are located using only global indexes.
	BY LOCAL INDEX ROWID	If the table is partitioned and rows are located using one or more local indexes and possibly some global indexes.
		Partition Boundaries: The partition boundaries may have been computed by: a previous PARTITION step, in which case the partition_start and partition_stop column values replicate the values present in the PARTITION step, and the partition_id contains the ID of the PARTITION step. Possible values for partition_start and partition_stop are NUMBER(n), KEY, INVALID. the TABLE ACCESS or INDEX step itself, in which case the partition_id contains the ID of the step. Possible values for partition_start and partition_stop are NUMBER(n), KEY, ROW LOCATION (TABLE ACCESS only), and INVALID.
UNION		Operation accepting two sets of rows and returns the union of the sets, eliminating duplicates.
VIEW		Operation performing a view's query and then returning the resulting rows to another operation.

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

EXPLAIN PLAN and Partitioned Objects

Use EXPLAIN PLAN to see how Oracle will access partitioned objects for specific queries.

Partitions accessed after pruning are shown in the PARTITION START and PARTITION STOP columns. The row source name for the range partition is "PARTITION RANGE". For hash partitions, the row source name is "PARTITION HASH".

A join is implemented using partial partition-wise join if the DISTRIBUTION column of the plan table of one of the joined tables contains "PARTITION(KEY)". Partial partition-wise join is possible if one of the joined tables is partitioned on its join column and the table is parallelized.

A join is implemented using full partition-wise join if the partition row source appears before the join row source in the EXPLAIN PLAN output. Full partition-wise joins are possible only if both joined tables are equi-partitioned on their respective join columns. Examples of execution plans for several types of partitioning follow.

Examples of How EXPLAIN PLAN Displays Range and Hash Partitioning

Consider the following table, EMP_RANGE, partitioned by range on HIREDATE to illustrate how pruning is displayed. Assume that the tables EMP and DEPT from a standard Oracle schema exist.

   CREATE TABLE EMP_RANGE 
    PARTITION BY RANGE(HIREDATE) 
    ( 
    PARTITION EMP_P1 VALUES LESS THAN (TO_DATE('1-JAN-1981','DD-MON-YYYY')),
    PARTITION EMP_P2 VALUES LESS THAN (TO_DATE('1-JAN-1983','DD-MON-YYYY')),
    PARTITION EMP_P3 VALUES LESS THAN (TO_DATE('1-JAN-1985','DD-MON-YYYY')),
    PARTITION EMP_P4 VALUES LESS THAN (TO_DATE('1-JAN-1987','DD-MON-YYYY')),
    PARTITION EMP_P5 VALUES LESS THAN (TO_DATE('1-JAN-1989','DD-MON-YYYY')) 
    ) 
    AS SELECT * FROM EMP;

Example 1:

   EXPLAIN PLAN FOR SELECT * FROM EMP_RANGE;

Then enter the following to display the EXPLAIN PLAN output:

        @?/RDBMS/ADMIN/UTLXPLS

Oracle displays something similar to:

Plan Table 
-------------------------------------------------------------------------------
| Operation               |  Name    |  Rows | Bytes|  Cost  | Pstart |  Pstop|
-------------------------------------------------------------------------------
| SELECT STATEMENT        |          |   105 |    8K|      1 |        |       |
|  PARTITION RANGE ALL    |          |       |      |        |     1  |     5 |
|   TABLE ACCESS FULL     |EMP_RANGE |   105 |    8K|      1 |     1  |     5 |
-------------------------------------------------------------------------------
6 rows selected.

A partition row source is created on top of the table access row source. It iterates over the set of partitions to be accessed.

In example 1, the partition iterator covers all partitions (option ALL) because a predicate was not used for pruning. The PARTITION_START and PARTITION STOP columns of the plan table show access to all partitions from 1 to 5.

Example 2:

   EXPLAIN PLAN FOR SELECT * FROM EMP_RANGE 
   WHERE HIREDATE >= TO_DATE('1-JAN-1985','DD-MON-YYYY'); 

Plan Table 
--------------------------------------------------------------------------------
| Operation                 | Name    |  Rows  | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     3 |   54 |      1 |       |       |
|  PARTITION RANGE ITERATOR |          |       |      |        |     4 |     5 |
|   TABLE ACCESS FULL       |EMP_RANGE |     3 |   54 |      1 |     4 |     5 |
--------------------------------------------------------------------------------
6 rows selected.

In example 2, the partition row source iterates from partition 4 to 5 because we prune the other partitions using a predicate on HIREDATE.

Example 3:

   EXPLAIN PLAN FOR SELECT * FROM EMP_RANGE 
   WHERE HIREDATE < TO_DATE('1-JAN-1981','DD-MON-YYYY'); 

Plan Table 
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     2 |   36 |      1 |       |       |
|  TABLE ACCESS FULL        |EMP_RANGE |     2 |   36 |      1 |     1 |     1 |
--------------------------------------------------------------------------------
5 rows selected.

In example 3, only partition 1 is accessed and known at compile time, thus there is no need for a partition row source.

Plans for Hash Partitioning

Oracle displays the same information for hash partitioned objects except that the partition row source name is "PARTITION HASH" instead of "PARTITION RANGE". Also, with hash partitioning, pruning is only possible using equality or in-list predicates.

Pruning Information with Composite Partitioned Objects

To illustrate how Oracle displays pruning information for composite partitioned objects, consider the table EMP_COMP that is range partitioned on HIREDATE and subpartitioned by hash on DEPTNO.

 CREATE TABLE EMP_COMP PARTITION BY RANGE(HIREDATE) SUBPARTITION BY HASH(DEPTNO) 
  SUBPARTITIONS 3 
  ( 
  PARTITION EMP_P1 VALUES LESS THAN (TO_DATE('1-JAN-1981','DD-MON-YYYY')),
  PARTITION EMP_P2 VALUES LESS THAN (TO_DATE('1-JAN-1983','DD-MON-YYYY')),
  PARTITION EMP_P3 VALUES LESS THAN (TO_DATE('1-JAN-1985','DD-MON-YYYY')),
  PARTITION EMP_P4 VALUES LESS THAN (TO_DATE('1-JAN-1987','DD-MON-YYYY')),
  PARTITION EMP_P5 VALUES LESS THAN (TO_DATE('1-JAN-1989','DD-MON-YYYY')) 
   ) 
  AS SELECT * FROM EMP;

Example 1:

   EXPLAIN PLAN FOR SELECT * FROM EMP_COMP; 

Plan Table 
--------------------------------------------------------------------------------
| Operation                 |  Name   |  Rows | Bytes|  Cost  | Pstart | Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |         |   105 |    8K|      1 |        |       |
|  PARTITION RANGE ALL      |         |       |      |        |     1  |     5 |
|   PARTITION HASH ALL      |         |       |      |        |     1  |     3 |
|    TABLE ACCESS FULL      |EMP_COMP |   105 |    8K|      1 |     1  |     15|
--------------------------------------------------------------------------------
7 rows selected.

Example 1 shows the explain plan when Oracle accesses all subpartitions of all partitions of a composite object. Two partition row sources are used for that purpose: a range partition row source to iterate over the partitions and a hash partition row source to iterate over the subpartitions of each accessed partition.

In this example, since no pruning is performed, the range partition row source iterates from partition 1 to 5. Within each partition, the hash partition row source iterates over subpartitions 1 to 3 of the current partition. As a result, the table access row source accesses subpartitions 1 to 15. In other words, it accesses all subpartitions of the composite object.

Example 2:

   EXPLAIN PLAN FOR SELECT * FROM EMP_COMP WHERE HIREDATE = 
   TO_DATE('15-FEB-1987', 'DD-MON-YYYY'); 

Plan Table 
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     1 |   96 |      1 |       |       |
|  PARTITION HASH ALL       |          |       |      |        |     1 |     3 |
|   TABLE ACCESS FULL       |EMP_COMP  |     1 |   96 |      1 |    13 |    15 |
--------------------------------------------------------------------------------
6 rows selected.

In example 2, only the last partition, partition 5, is accessed. This partition is known at compile time so we do not need to show it in the plan. The hash partition row source shows accessing of all subpartitions within that partition, that is, subpartitions 1 to 3, which translates into subpartitions 13 to 15 of the EMP_COMP table.

Example 3:

   EXPLAIN PLAN FOR SELECT * FROM EMP_COMP WHERE DEPTNO = 20; 

Plan Table 
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     2 |  200 |      1 |       |       |
|  PARTITION RANGE ALL      |          |       |      |        |     1 |     5 |
|   TABLE ACCESS FULL       |EMP_COMP  |     2 |  200 |      1 |       |       |
--------------------------------------------------------------------------------
6 rows selected.

In this example, the predicate "DEPTNO = 20" enables pruning on the hash dimension within each partition, so Oracle only needs to access a single subpartition. The number of that subpartition is known at compile time so the hash partition row source is not needed.

Example 4:

   VARIABLE DNO NUMBER; 
   EXPLAIN PLAN FOR SELECT * FROM EMP_COMP WHERE DEPTNO = :DNO; 

Plan Table 
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     2 |  200 |      1 |       |       |
|  PARTITION RANGE ALL      |          |       |      |        |     1 |     5 |
|   PARTITION HASH SINGLE   |          |       |      |        |   KEY |   KEY |
|    TABLE ACCESS FULL      |EMP_COMP  |     2 |  200 |      1 |       |       |
--------------------------------------------------------------------------------
 7 rows selected.

Example 4 is the same as example 3 except that "DEPTNO = 20" has been replaced by "DEPTNO = :DNO". In this case, the subpartition number is unknown at compile time and a hash partition row source is allocated. The option is SINGLE for that row source because Oracle accesses only one subpartition within each partition. The PARTITION START and PARTITION STOP is set to "KEY". This means Oracle will determine the number of the subpartition at run time.

Partial Partition-wise Joins

Example 1:

In the following example, EMP_RANGE is joined on the partitioning column and is parallelized. This enables use of partial partition-wise join because the DEPT table is not partitioned. Oracle dynamically partitions the DEPT table before the join.

   ALTER TABLE EMP PARALLEL 2; 
      STATEMENT PROCESSED.
   ALTER TABLE DEPT PARALLEL 2; 
      STATEMENT PROCESSED.

To show the plan for the query, enter:

   EXPLAIN PLAN FOR SELECT /*+ ORDERED USE_HASH(D) */ ENAME, DNAME 
     FROM EMP_RANGE E, DEPT D 
     WHERE E.DEPTNO = D.DEPTNO 
     AND E.HIREDATE > TO_DATE('29-JUN-1986','DD-MON-YYYY'); 

    
Plan Table 
------------------------------------------------------------------------------------------------------------ 
| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| PQ Distrib | Pstart| Pstop | 
------------------------------------------------------------------------------------------------------------ 
| SELECT STATEMENT           |          |     1 |   51 |      3 |      |      |            |       |       | 
|  HASH JOIN                 |          |     1 |   51 |      3 | 2,02 | P->S | QC (RANDOM)|       |       | 
|   PARTITION RANGE ITERATOR |          |       |      |        | 2,02 | PCWP |            |     4 |     5 | 
|    TABLE ACCESS FULL       |EMP_RANGE |     3 |   87 |      1 | 2,00 | PCWP |            |     4 |     5 | 
|    TABLE ACCESS FULL       |DEPT      |    21 |  462 |      1 | 2,01 | P->P |PART (KEY)  |       |       | 
------------------------------------------------------------------------------------------------------------ 
8 rows selected.

The plan shows that the optimizer select partition-wise join because the DIST column contains the text "PART (KEY)", or, partition key.

Example 2:

In example 2, EMP_COMP is joined on its hash partitioning column, DEPTNO, and is parallelized. This enables use of partial partition-wise join because the DEPT table is not partitioned. Again, Oracle dynamically partitions the DEPT table.

   ALTER TABLE EMP_COMP PARALLEL 2; 
     STATEMENT PROCESSED. 
   EXPLAIN PLAN FOR SELECT /*+ ORDERED USE_HASH(D) */ ENAME, DNAME 
     FROM EMP_COMP E, DEPT D 
     WHERE E.DEPTNO = D.DEPTNO 
     AND E.HIREDATE > TO_DATE('13-MAR-1985','DD-MON-YYYY'); 

    
Plan Table
------------------------------------------------------------------------------------------------------------ 
| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| PQ Distrib | Pstart| Pstop | 
------------------------------------------------------------------------------------------------------------ 
| SELECT STATEMENT           |          |    1  |  51  |      3 |      |      |            |       |       |
|  HASH JOIN                 |          |     1 |   51 |      3 | 0,01 | P->S | QC (RANDOM)|       |       | 
|   PARTITION RANGE ITERATOR |          |       |      |        | 0,01 | PCWP |            |     4 |     5 | 
|    PARTITION HASH ALL      |          |       |      |        | 0,01 | PCWP |            |     1 |     3 | 
|     TABLE ACCESS FULL      |EMP_COMP  |     3 |   87 |      1 | 0,01 | PCWP |            |    10 |    15 | 
|   TABLE ACCESS FULL        |DEPT      |    21 |  462 |      1 | 0,00 | P->P | PART (KEY) |       |       | 
------------------------------------------------------------------------------------------------------------ 
9 rows selected.

Full Partition-wise Joins

In the following example, EMP_COMP and DEPT_HASH are joined on their hash partitioning columns. This enables use of full partition-wise join. The "PARTITION HASH" row source appears on top of the join row source in the plan table output.

To create the table DEPT_HASH, enter:

   CREATE TABLE DEPT_HASH 
     PARTITION BY HASH(deptno) 
     PARTITIONS 3 
     PARALLEL 
     AS SELECT * FROM DEPT;

To show the plan for the query, enter:

   EXPLAIN PLAN FOR SELECT /*+ ORDERED USE_HASH(D) */ ENAME, DNAME 
     FROM EMP_COMP E, DEPT_HASH D 
     WHERE E.DEPTNO = D.DEPTNO 
     AND E.HIREDATE > TO_DATE('29-JUN-1986','DD-MON-YYYY'); 

    
Plan Table 
------------------------------------------------------------------------------------------------------------ 
| Operation                   |  Name    |  Rows | Bytes|  Cost  |  TQ |IN-OUT| PQ Distrib | Pstart| Pstop | 
------------------------------------------------------------------------------------------------------------ 
| SELECT STATEMENT            |          |     2 |   102|      2 |     |      |            |       |       |
|  PARTITION HASH ALL         |          |       |      |        | 4,00| PCWP |            |     1 |     3 | 
|   HASH JOIN                 |          |     2 |  102 |      2 | 4,00| P->S | QC (RANDOM)|       |       | 
|    PARTITION RANGE ITERATOR |          |       |      |        | 4,00| PCWP |            |     4 |     5 | 
|     TABLE ACCESS FULL       |EMP_COMP  |     3 |   87 |      1 | 4,00| PCWP |            |    10 |    15 | 
|    TABLE ACCESS FULL        |DEPT_HASH |    63 |    1K|      1 | 4,00| PCWP |            |     1 |     3 | 
------------------------------------------------------------------------------------------------------------ 
9 rows selected.

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 appears as follows:

   OPERATION          OPTIONS           OBJECT_NAME
   ----------------   ---------------   -------------- 
   SELECT STATEMENT
   INLIST ITERATOR
   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         INLIST                       KEY(INLIST)       KEY(INLIST)
 INLIST ITERATOR
 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
 PARTITION         ITERATOR                     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                                      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
 TABLE ACCESS       BY INDEX ROWID    EMP
 BITMAP CONVERSION  TO ROWIDS
 BITMAP INDEX       SINGLE VALUE      EMP_EMPNO

DOMAIN INDEX and EXPLAIN PLAN

You can also use EXPLAIN PLAN to derive user-defined CPU and I/O costs for domain indexes. EXPLAIN PLAN displays these statistics in the "OTHER" column of PLAN_TABLE.

For example, assume table EMP has user-defined operator CONTAINS with a domain index EMP_RESUME on the RESUME column and the index type of EMP_RESUME supports the operator CONTAINS. Then the query:

 SELECT * from EMP where Contains(resume, 'Oracle') = 1

might display the following plan:

 	OPERATION            OPTIONS      OBJECT_NAME     OTHER 
 -----------------    -----------  ------------    ----------------
 SELECT STATEMENT 
 TABLE ACCESS         BY ROWID     EMP
 DOMAIN INDEX                      EMP_RESUME      CPU: 300, I/O: 4

Formatting EXPLAIN PLAN Output

This section shows options for formatting EXPLAIN PLAN output

Using the EXPLAIN PLAN Statement
Selecting PLAN_TABLE Output in Table Format

Selecting PLAN_TABLE Output in Nested Format

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

Using the EXPLAIN PLAN Statement

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:

            SELECT operation, options, object_name, id, parent_id, position, cost, cardinality,
            other_tag, optimizer 
               FROM plan_table
               WHERE statement_id = 'Emp_Sal'
               ORDER BY id;

Generates this output:

  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 6, 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, as illustrated in Figure 13-1.

Figure 13-1 Tree Structure of an Execution Plan

Tree structures illustrate how SQL statement execution operations feed one another. Oracle assigns each step in the execution plan a number representing the ID column of the PLAN_TABLE. Each step 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

Oracle does not support EXPLAIN PLAN for statements performing 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 types 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.

13 Using EXPLAIN PLAN

Introduction to EXPLAIN PLAN

Creating the Output Table

Displaying PLAN_TABLE Output

Output Table Columns

Table 13-1 PLAN_TABLE Columns

Table 13-2 Values of OTHER_TAG Column of the PLAN_TABLE

Table 13-3 Values of DISTRIBUTION Column of the PLAN_TABLE

Table 13-4 OPERATION and OPTION Values Produced by EXPLAIN PLAN

Bitmap Indexes and EXPLAIN PLAN

EXPLAIN PLAN and Partitioned Objects

Examples of How EXPLAIN PLAN Displays Range and Hash Partitioning

Plans for Hash Partitioning

Pruning Information with Composite Partitioned Objects

Partial Partition-wise Joins

Full Partition-wise Joins

INLIST ITERATOR and EXPLAIN PLAN

Index Column

Index and Partition Column

Partition Column

DOMAIN INDEX and EXPLAIN PLAN

Formatting EXPLAIN PLAN Output

Using the EXPLAIN PLAN Statement

Selecting PLAN_TABLE Output in Table Format

Selecting PLAN_TABLE Output in Nested Format

Figure 13-1 Tree Structure of an Execution Plan

EXPLAIN PLAN Restrictions

13
Using EXPLAIN PLAN