9
Using EXPLAIN PLAN

This chapter introduces execution plans, describes the SQL statement EXPLAIN PLAN, and explains how to interpret its output. This chapter also provides procedures for managing outlines to control application performance characteristics.

This chapter contains the following sections:

Understanding 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 run the statement.

The row source tree is the core of the execution plan. It shows the following information:

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
Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

Optimization, such as the cost and cardinality of each operation
Partitioning, such as the set of accessed partitions
Parallel execution, such as the distribution method of join inputs

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan (for example, nested loops join). It also helps you to understand the optimizer decisions (for example, why the optimizer chose a nested loops join instead of a hash join), and explains the performance of a query.

Note:

Oracle Performance Manager charts and Oracle SQL Analyze can automatically create and display explain plans for you. For more information on using explain plans, see the Database Tuning with the Oracle Tuning Pack manual.

How Execution Plans Can Change

With the cost-based optimizer, execution plans can and do change as the underlying costs change. EXPLAIN PLAN output shows how Oracle runs the SQL statement when the statement was explained. This can differ from the plan during actual execution for a SQL statement, because of differences in the execution environment and explain plan environment.

Execution plans can differ due to the following:

Different Schemas

The execution and explain plan happen on different databases.
The user explaining the statement is different from the user running the statement. Two users might be pointing to different objects in the same database, resulting in different execution plans.
Schema changes (usually changes in indexes) between the two operations.

Different Costs

Even if the schemas are the same, the optimizer can choose different execution plans if the costs are different. Some factors that affect the costs include the following:

Data volume and statistics
Bind variable types
Initialization parameters (changed globally or at session level)

Looking Beyond Execution Plans

The execution plan operation alone cannot differentiate between well-tuned statements and those that perform poorly. For example, an EXPLAIN PLAN output that shows that a statement uses an index does not necessarily mean that the statement runs efficiently. Sometimes indexes can be extremely inefficient. In this case, you should examine the following:

The columns of the index being used
Their selectivity (fraction of table being accessed)

It is best to use EXPLAIN PLAN to determine an access plan, and then later prove that it is the optimal plan through testing. When evaluating a plan, examine the statement's actual resource consumption. Use Oracle Trace or the SQL trace facility and TKPROF to examine individual SQL statement performance.

See Also:

Chapter 10, "Using SQL Trace and TKPROF" for information on TKPROF interpretation

Creating the PLAN_TABLE Output Table

Before issuing an EXPLAIN PLAN statement, you must have a table to hold it's output.

Use 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. For example, it is located under $ORACLE_HOME/rdbms/admin on Unix. PLAN_TABLE is the default table into which the EXPLAIN PLAN statement inserts rows describing execution plans.

Oracle Corporation recommends that you drop and rebuild the PLAN_TABLE table after upgrading the version of the database. This is because the columns might change, which can cause scripts (or TKPROF, if you are specifying the table) to fail.

If you want an output table with a different name, then create PLAN_TABLE and rename it with the RENAME SQL statement.

Running EXPLAIN PLAN

To explain a SQL statement, enter the following:

EXPLAIN PLAN FOR
<<SQL Statement>>

This explains the plan into the PLAN_TABLE table. You can then select the execution plan from PLAN_TABLE. This is useful if you do not have any other plans in PLAN_TABLE, or if you only want to look at the last statement.

For example:

EXPLAIN PLAN FOR
SELECT name FROM emp;

Identifying Statements for EXPLAIN PLAN

With multiple statements, you can specify a statement identifier and use that to identify your specific execution plan. Before using a statement ID, remove any existing rows for that statement ID.

For example:

EXPLAIN PLAN
    SET STATEMENT_ID = <<identifier>> FOR
<<SQL Statement>>

Specifically:

EXPLAIN PLAN
    SET STATEMENT_ID = 'bad1' FOR
SELECT name FROM emp;

Specifying Different Tables for EXPLAIN PLAN

You can specify the INTO clause to specify a different table.

For example:

EXPLAIN PLAN
   INTO <<different table>>
 FOR <<SQL Statement>>

Specifically:

EXPLAIN PLAN
   INTO my_plan_table
    FOR
SELECT name FROM emp;

Or:

EXPLAIN PLAN
   INTO my_plan_table
    SET STATEMENT_ID = 'bad1' FOR
SELECT name FROM emp;

See Also:

Oracle9i Database Reference for a complete description of EXPLAIN PLAN syntax.

Displaying PLAN_TABLE Output

After you have explained the plan, use the two scripts provided by Oracle to display the most recent plan table output:

UTLXPLS.SQL - Shows plan table output for serial processing
UTLXPLP.SQL - Shows plan table output with parallel execution columns

If you have specified a statement identifier, then you can write your own script to query the PLAN_TABLE.

For example:

Start with ID = 0 and given STATEMENT_ID.
Use the CONNECT BY clause to walk the tree from parent to child, the join keys being STATEMENT_ID = PRIOR STATEMENT_ID and PARENT_ID = PRIOR ID.

Use the pseudo-column LEVEL (associated with CONNECT BY) to indent the children.

SELECT cardinality "Rows",
       lpad(' ',level-1)||operation||' '||
       options||' '||object_name "Plan"
  FROM PLAN_TABLE

CONNECT BY prior id = parent_id
        AND prior statement_id = statement_id
  START WITH id = 0
        AND statement_id = 'bad1'
  ORDER BY id;

   Rows Plan
------- ----------------------------------------
        SELECT STATEMENT
         TABLE ACCESS FULL EMP

The NULL in the Rows column indicates that the optimizer does not have any statistics on the table. Analyzing the table shows the following:

   Rows Plan
------- ----------------------------------------
  16957 SELECT STATEMENT
  16957  TABLE ACCESS FULL EMP

You can also select the COST. This is useful for comparing execution plans or for understanding why the optimizer chooses one execution plan over another.

Reading EXPLAIN PLAN Output

This section uses progressively complex examples to illustrate execution plans.

The following statement is used to display the execution plan:

SELECT lpad(' ',level-1)||operation||' '||options||' '||
        object_name "Plan"
   FROM plan_table
CONNECT BY prior id = parent_id
        AND prior statement_id = statement_id
  START WITH id = 0 AND statement_id = '&1'
  ORDER BY id;

EXPLAIN PLAN Example 1

EXPLAIN PLAN SET statement_id = 'example1' FOR
SELECT full_name FROM per_all_people_f
 WHERE UPPER(full_name) LIKE 'Pe%' ;

Plan
---------------------------------------------
SELECT STATEMENT
 TABLE ACCESS FULL PER_ALL_PEOPLE_F

This plan shows execution of a SELECT statement. The table PER_ALL_PEOPLE_F is accessed using a full table scan.

Every row in the table PER_ALL_PEOPLE_F is accessed, and the WHERE clause criteria is evaluated for every row.
The SELECT statement returns the rows meeting the WHERE clause criteria.

EXPLAIN PLAN Example 2

EXPLAIN PLAN SET statement_id = 'example2' FOR
SELECT full_name FROM per_all_people_f
 WHERE full_name LIKE 'Pe%' ;

Plan
---------------------------------------------
SELECT STATEMENT
 TABLE ACCESS BY INDEX ROWID PER_ALL_PEOPLE_F
  INDEX RANGE SCAN PER_PEOPLE_F_N54

This plan shows execution of a SELECT statement.

Index PER_PEOPLE_F_N54 is used in a range scan operation.
The table PER_ALL_PEOPLE_F is accessed via ROWID. ROWIDs are obtained from the index in the previous step for keys that meet the WHERE clause criteria. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated.
The SELECT statement returns rows satisfying the WHERE clause conditions (evaluated in previous steps).

EXPLAIN PLAN Example 3

EXPLAIN PLAN SET statement_id = 'example3' FOR
SELECT segment1, segment2, description, inventory_item_id
  FROM mtl_system_items msi
 WHERE segment1 = :b1
   AND segment2 LIKE '%-BOM'
AND NVL(end_date_active,sysdate+1) > SYSDATE ;

Plan
--------------------------------------------------
SELECT STATEMENT
 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS
  INDEX RANGE SCAN MTL_SYSTEM_ITEMS_N8

This plan shows execution of a SELECT statement.

Index MTL_SYSTEM_ITEMS_N8 is used in a range scan operation. This is an index on (SEGMENT1, SEGMENT2, SEGMENT3). The range scan happens using the following condition:
```
segment1 = :b1
```
The rows that come out of this step satisfy all the WHERE clause criteria that can be evaluated with the index columns. Therefore, the following condition is also evaluated at this stage:
```
segment2 LIKE '%-BOM' 
```
The table PER_ALL_PEOPLE_F is accessed via ROWIDs obtained from the index in the previous step. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. Therefore, the following condition is evaluated at this stage:
```
NVL(end_date_active,sysdate+1) > SYSDATE
```
The SELECT statement returns rows satisfying the WHERE clause conditions (evaluated in previous steps).

EXPLAIN PLAN Example 4

EXPLAIN PLAN SET statement_id = 'example4' FOR
SELECT h.order_number, l.revenue_amount, l.ordered_quantity
  FROM so_headers_all h, so_lines_all l
 WHERE h.customer_id = :b1
   AND h.date_ordered > SYSDATE-30
   AND l.header_id = h.header_id ;

Plan
--------------------------------------------------
SELECT STATEMENT
 NESTED LOOPS
  TABLE ACCESS BY INDEX ROWID SO_HEADERS_ALL
   INDEX RANGE SCAN SO_HEADERS_N1
  TABLE ACCESS BY INDEX ROWID SO_LINES_ALL
   INDEX RANGE SCAN SO_LINES_N1

This plan shows execution of a SELECT statement.

Index so_headers_n1 is used in a range scan operation. This is an index on customer_id. The range scan happens using the following condition:
```
customer_id = :b1
```
The table so_headers_all is accessed via ROWIDs obtained from the index in the previous step. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. Therefore, the following condition is evaluated at this stage:
```
h.date_ordered > sysdate-30
```
For every row from so_headers_all satisfying the WHERE clause conditions, a range scan is run on so_lines_n1 using the following condition:
```
l.header_id = h.header_id
```
The table so_lines_all is accessed via ROWIDs obtained from the index in the previous step. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. There are no additional conditions to evaluate here.
The SELECT statement returns rows satisfying the WHERE clause conditions (evaluated in previous steps).

Viewing Bitmap Indexes with EXPLAIN PLAN

Index row sources using bitmap indexes appear in the EXPLAIN PLAN output with the word BITMAP indicating the type of the index. Consider the following sample query and plan:

EXPLAIN PLAN FOR

SELECT * FROM t
WHERE c1 = 2 
AND c2 <> 6 
OR c3 BETWEEN 10 AND 20;


SELECT STATEMENT
   TABLE ACCESS T BY INDEX ROWID
      BITMAP CONVERSION TO ROWID
         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. The TO ROWIDS option is used to generate the ROWIDs that are necessary for the table access.

Note:

Queries using bitmap join index indicate the bitmap join index access path. The operation for bitmap join index is the same as bitmap index.

Viewing Partitioned Objects with EXPLAIN PLAN

Use EXPLAIN PLAN to see how Oracle accesses 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 Displaying Range and Hash Partitioning with EXPLAIN PLAN

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-1991','DD-MON-YYYY')),
PARTITION emp_p2 VALUES LESS THAN (TO_DATE('1-JAN-1993','DD-MON-YYYY')),
PARTITION emp_p3 VALUES LESS THAN (TO_DATE('1-JAN-1995','DD-MON-YYYY')),
PARTITION emp_p4 VALUES LESS THAN (TO_DATE('1-JAN-1997','DD-MON-YYYY')),
PARTITION emp_p5 VALUES LESS THAN (TO_DATE('1-JAN-1999','DD-MON-YYYY')) 

) 
AS SELECT * FROM emp;

For the first example, consider the following statement:

EXPLAIN PLAN FOR SELECT * FROM emp_range;

Enter the following to display the EXPLAIN PLAN output:

@?/RDBMS/ADMIN/UTLXPLS

Oracle displays something similar to the following:

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 the above example, 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.

For the next example, consider the following statement:

EXPLAIN PLAN FOR SELECT * FROM emp_range 
WHERE hiredate >= TO_DATE('1-JAN-1995','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 the above example, the partition row source iterates from partition 4 to 5, because we prune the other partitions using a predicate on hiredate.

Finally, consider the following statement:

EXPLAIN PLAN FOR SELECT * FROM emp_range 
WHERE hiredate < TO_DATE('1-JAN-1991','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 the above example, 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 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.

Examples of 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-1991','DD-MON-YYYY')),
PARTITION emp_p2 VALUES LESS THAN (TO_DATE('1-JAN-1993','DD-MON-YYYY')),
PARTITION emp_p3 VALUES LESS THAN (TO_DATE('1-JAN-1995','DD-MON-YYYY')),
PARTITION emp_p4 VALUES LESS THAN (TO_DATE('1-JAN-1997','DD-MON-YYYY')),
PARTITION emp_p5 VALUES LESS THAN (TO_DATE('1-JAN-1999','DD-MON-YYYY')) 

) 
AS SELECT * FROM emp;

For the first example, consider the following statement:

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.

This example shows the 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 the following example, the range partition row source iterates from partition 1 to 5, because no pruning is performed. 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.

EXPLAIN PLAN FOR SELECT * FROM emp_comp 
WHERE hiredate = TO_DATE('15-FEB-1997', '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 the above example, 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.

Now consider the following statement:

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 the above 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.

Finally, consider the following statement:

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.

The last two examples are the same, except that deptno = 20 has been replaced by deptno = :dno. In this last 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 that Oracle determines the number of the subpartition at run time.

Examples of Partial Partition-wise Joins

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; 

Table altered.

ALTER TABLE dept PARALLEL 2; 

Table altered.

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-1996','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 selects partition-wise join, because the PQ Distrib column contains the text PART (KEY), or partition key.

In the next example, 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; 

Table altered. 

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-1995','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.

Examples of 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-1996','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.

Examples of 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:

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.

When the IN-List Column is an 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 RANGE  ALL                               KEY(INLIST)     KEY(INLIST)
INLIST ITERATOR
TABLE ACCESS     BY LOCAL INDEX 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.

When the IN-List Column is an Index and a 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 RANGE  ITERATOR                         KEY(INLIST)     KEY(INLIST)
TABLE ACCESS     BY LOCAL INDEX ROWID EMP         KEY(INLIST)     KEY(INLIST)
INDEX            RANGE SCAN           EMP_EMPNO   KEY(INLIST)     KEY(INLIST)

When the IN-List Column is a 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 RANGE   INLIST                       KEY(INLIST)       KEY(INLIST)
TABLE ACCESS      FULL           EMP           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

Example of Domain Indexes 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

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

PLAN_TABLE Columns

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

Table 9-1 PLAN_TABLE Columns* (Page 1 of 4)*

Column	Type	Description
STATEMENT_ID	VARCHAR2(30)	The value of the optional `STATEMENT_ID` parameter specified in the `EXPLAIN` `PLAN` statement.
TIMESTAMP	DATE	The date and time when the `EXPLAIN` `PLAN` statement was issued.
REMARKS	VARCHAR2(80)	Any comment (of up to 80 bytes) you want 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`, then use the `UPDATE` statement to modify the rows of the `PLAN_TABLE`.
OPERATION	VARCHAR2(30)	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 9-4 for more information on values for this column.
OPTIONS	VARCHAR2(225)	A variation on the operation described in the `OPERATION` column. See Table 9-4 for more information on values for this column.
OBJECT_NODE	VARCHAR2(128)	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	VARCHAR2(30)	The name of the user who owns the schema containing the table or index.
OBJECT_NAME	VARCHAR2(30)	The name of the table or index.
OBJECT_INSTANCE	NUMERIC	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 results in unpredictable numbers.
OBJECT_TYPE	VARCHAR2(30)	A modifier that provides descriptive information about the object; for example, `NON`-`UNIQUE` for indexes.
OPTIMIZER	VARCHAR2(255)	The current mode of the optimizer.
SEARCH_COLUMNS	NUMBERIC	Not currently used.
ID	NUMERIC	A number assigned to each step in the execution plan.
PARENT_ID	NUMERIC	The ID of the next execution step that operates on the output of the `ID` step.
POSITION	NUMERIC	For the first row of output, this indicates the optimizer's estimated cost of executing the statement. For the other rows, it indicates the position relative to the other children of the same parent.
COST	NUMERIC	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. The value of this column is a function of the `CPU_COST` and `IO_COST` columns.
CARDINALITY	NUMERIC	The estimate by the cost-based approach of the number of rows accessed by the operation.
BYTES	NUMERIC	The estimate by the cost-based approach of the number of bytes accessed by the operation.
OTHER_TAG	VARCHAR2(255)	Describes the contents of the `OTHER` column. See Table 9-2 for more information on the possible values for this column.
PARTITION_START	VARCHAR2(255)	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 run time from partitioning key values. `ROW` `LOCATION` indicates that the start partition (same as the stop partition) will be computed at run 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.
PARTITION_STOP	VARCHAR2(255)	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 run time from partitioning key values. `ROW` `LOCATION` indicates that the stop partition (same as the start partition) will be computed at run 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.
PARTITION_ID	NUMERIC	The step that has computed the pair of values of the `PARTITION_START` and `PARTITION_STOP` columns.
OTHER	LONG	Other information that is specific to the execution step that a user might find useful.
DISTRIBUTION	VARCHAR2(30)	Stores the method used to distribute rows from producer query servers to consumer query servers. See Table 9-3 for more information on the possible values for this column. For more information about consumer and producer query servers, see Oracle9i Database Concepts.
CPU_COST	NUMERIC	The CPU 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. The value of this column is proportional to the number of machine cycles required for the operation.
IO_COST	NUMERIC	The I/O 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. The value of this column is proportional to the number of data blocks read by the operation.
TEMP_SPACE	NUMERIC	The temporary space, in bytes, used by the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, or for operations that don't use any temporary space, this column is null.

Table 9-2 describes the values that can appear in the OTHER_TAG column.

Table 9-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 9-3 describes the values that can appear in the DISTRIBUTION column:

Table 9-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 or index using the rowid of the row to `UPDATE`/`DELETE`.
PARTITION (KEY)	Maps rows to query servers based on the partitioning of a table or 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 9-4 lists each combination of OPERATION and OPTION produced by the EXPLAIN PLAN statement and its meaning within an execution plan.

Table 9-4 OPERATION and OPTION Values Produced by EXPLAIN PLAN (Page 1 of 4)

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 "Viewing Bitmap Indexes with 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. The options column contain information supplied by a user-defined domain index cost function, if any.
FILTER		Operation accepting a set of rows, eliminates some of them, and returns the rest.
FIRST ROW		Retrieval of 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 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 `IN`-list 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 `PARTITION_START` and `PARTITION_STOP` of the `PARTITION`. Refer to Table 9-1 for valid values of partition start/stop.
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 are access methods.)	FULL	Retrieval of all rows from a table.
	SAMPLE	Retrieval of sampled 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 RANGE	Retrieval of rows from a table based on a rowid range.
	SAMPLE BY ROWID RANGE	Retrieval of sampled rows from a table based on a rowid range.
	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 might 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.

9 Using EXPLAIN PLAN

Understanding EXPLAIN PLAN

How Execution Plans Can Change

Different Schemas

Different Costs

Looking Beyond Execution Plans

Creating the PLAN_TABLE Output Table

Running EXPLAIN PLAN

Identifying Statements for EXPLAIN PLAN

Specifying Different Tables for EXPLAIN PLAN

Displaying PLAN_TABLE Output

Reading EXPLAIN PLAN Output

EXPLAIN PLAN Example 1

EXPLAIN PLAN Example 2

EXPLAIN PLAN Example 3

EXPLAIN PLAN Example 4

Viewing Bitmap Indexes with EXPLAIN PLAN

Viewing Partitioned Objects with EXPLAIN PLAN

Examples of Displaying Range and Hash Partitioning with EXPLAIN PLAN

Plans for Hash Partitioning

Examples of Pruning Information with Composite Partitioned Objects

Examples of Partial Partition-wise Joins

Examples of Full Partition-wise Joins

Examples of INLIST ITERATOR and EXPLAIN PLAN

When the IN-List Column is an Index Column

When the IN-List Column is an Index and a Partition Column

When the IN-List Column is a Partition Column

Example of Domain Indexes and EXPLAIN PLAN

EXPLAIN PLAN Restrictions

PLAN_TABLE Columns

Table 9-1 PLAN_TABLE Columns (Page 1 of 4)

Table 9-2 Values of OTHER_TAG Column of the PLAN_TABLE

Table 9-3 Values of DISTRIBUTION Column of the PLAN_TABLE

Table 9-4 OPERATION and OPTION Values Produced by EXPLAIN PLAN (Page 1 of 4)

9
Using EXPLAIN PLAN

Table 9-1 PLAN_TABLE Columns* (Page 1 of 4)*