7 PLAN_TABLE Reference

This chapter describes PLAN_TABLE columns.

7.1 PLAN_TABLE Columns

PLAN_TABLE is populated by the EXPLAIN PLAN statement.

The following table describes the columns in PLAN_TABLE.

Table 7-1 PLAN_TABLE Columns

Column Type Description

STATEMENT_ID

VARCHAR2(30)

Value of the optional STATEMENT_ID parameter specified in the EXPLAIN PLAN statement.

PLAN_ID

NUMBER

Unique identifier of a plan in the database.

TIMESTAMP

DATE

Date and time when the EXPLAIN PLAN statement was generated.

REMARKS

VARCHAR2(80)

Any comment (of up to 80 bytes) you want to associate with each step of the explained plan. This column indicates whether the database used an outline or SQL profile for the query.

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)

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 "OPERATION and OPTION Columns of PLAN_TABLE" for more information about values for this column.

OPTIONS

VARCHAR2(225)

A variation on the operation that the OPERATION column describes.

See "OPERATION and OPTION Columns of PLAN_TABLE" for more information about values for this column.

OBJECT_NODE

VARCHAR2(128)

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 the database consumes output from operations.

OBJECT_OWNER

VARCHAR2(30)

Name of the user who owns the schema containing the table or index.

OBJECT_NAME

VARCHAR2(30)

Name of the table or index.

OBJECT_ALIAS

VARCHAR2(65)

Unique alias of a table or view in a SQL statement. For indexes, it is the object alias of the underlying table.

OBJECT_INSTANCE

NUMERIC

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 for the original statement text. View expansion results in unpredictable numbers.

OBJECT_TYPE

VARCHAR2(30)

Modifier that provides descriptive information about the object; for example, NONUNIQUE for indexes.

OPTIMIZER

VARCHAR2(255)

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.

DEPTH

NUMERIC

Depth of the operation in the row source tree that the plan represents. You can use this value to indent the rows in a plan table report.

POSITION

NUMERIC

For the first row of output, this indicates the 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

Cost of the operation as estimated by the optimizer. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is 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

Estimate by the query optimization approach of the number of rows that the operation accessed.

BYTES

NUMERIC

Estimate by the query optimization approach of the number of bytes that the operation accessed.

OTHER_TAG

VARCHAR2(255)

Describes the contents of the OTHER column. Values are:

  • SERIAL (blank): Serial execution. Currently, SQL is not loaded in the OTHER column for this case.

  • SERIAL_FROM_REMOTE (S -> R): Serial execution at a remote site.

  • PARALLEL_FROM_SERIAL (S -> P): Serial execution. Output of step is partitioned or broadcast to parallel execution servers.

  • PARALLEL_TO_SERIAL (P -> S): Parallel execution. Output of step is returned to serial QC process.

  • PARALLEL_TO_PARALLEL (P -> P): Parallel execution. Output of step is repartitioned to second set of parallel execution servers.

  • PARALLEL_COMBINED_WITH_PARENT (PWP): Parallel execution; Output of step goes to next step in same parallel process. No interprocess communication to parent.

  • PARALLEL_COMBINED_WITH_CHILD (PWC): Parallel execution. Input of step comes from prior step in same parallel process. No interprocess communication from child.

PARTITION_START

VARCHAR2(255)

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 is identified at run time from partitioning key values.

ROW LOCATION indicates that the database computes the start partition (same as the stop partition) at run time from the location of each retrieved record. The record location is obtained by a user-specified ROWID or from a global index.

INVALID indicates that the range of accessed partitions is empty.

PARTITION_STOP

VARCHAR2(255)

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 is identified at run time from partitioning key values.

ROW LOCATION indicates that the database computes the stop partition (same as the start partition) at run time from the location of each retrieved record. 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

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. See the OTHER_TAG column.

DISTRIBUTION

VARCHAR2(30)

Method used to distribute rows from producer query servers to consumer query servers.

See "DISTRIBUTION Column of PLAN_TABLE" for more information about the possible values for this column. For more information about consumer and producer query servers, see Oracle Database VLDB and Partitioning Guide.

CPU_COST

NUMERIC

CPU cost of the operation as estimated by the optimizer. The value of this column is proportional to the number of machine cycles required for the operation. For statements that use the rule-based approach, this column is null.

IO_COST

NUMERIC

I/O cost of the operation as estimated by the optimizer. The value of this column is proportional to the number of data blocks read by the operation. For statements that use the rule-based approach, this column is null.

TEMP_SPACE

NUMERIC

Temporary space, in bytes, used by the operation as estimated by the optimizer. For statements that use the rule-based approach, or for operations that do not use any temporary space, this column is null.

ACCESS_PREDICATES

VARCHAR2(4000)

Predicates used to locate rows in an access structure. For example, start or stop predicates for an index range scan.

FILTER_PREDICATES

VARCHAR2(4000)

Predicates used to filter rows before producing them.

PROJECTION

VARCHAR2(4000)

Expressions produced by the operation.

TIME

NUMBER(20,2)

Elapsed time in seconds of the operation as estimated by query optimization. For statements that use the rule-based approach, this column is null.

QBLOCK_NAME

VARCHAR2(30)

Name of the query block, either system-generated or defined by the user with the QB_NAME hint.

"OPERATION and OPTION Columns of PLAN_TABLE" lists each combination of OPERATION and OPTIONS produced by the EXPLAIN PLAN statement and its meaning within an execution plan.

See Also:

Oracle Database Reference for more information about PLAN_TABLE

7.2 OPERATION and OPTION Columns of PLAN_TABLE

This table lists each combination of the OPERATION and OPTIONS columns of the PLAN_TABLE and their meaning within an execution plan.

Table 7-2 OPERATION and OPTIONS 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.

BITMAP

CONVERSION

TO ROWIDS converts bitmap representations to actual rowids that you can use 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.

BITMAP

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.

BITMAP

MERGE

Merges several bitmaps resulting from a range scan into one bitmap.

BITMAP

MINUS

Subtracts bits of one bitmap from another. Row source is used for negated predicates. This option is usable only if there are non-negated predicates yielding a bitmap from which the subtraction can take place.

BITMAP

OR

Computes the bitwise OR of two bitmaps.

BITMAP

AND

Computes the bitwise AND of two bitmaps.

BITMAP

KEY ITERATION

Takes each row from a table row source and finds the corresponding bitmap from a bitmap index. This set of bitmaps are then merged into one bitmap in a following BITMAP MERGE operation.

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.

CUBE JOIN

Joins a table or view on the left and a cube on the right.

See Oracle Database SQL Language Reference to learn about the NO_USE_CUBE and USE_CUBE hints.

CUBE JOIN

ANTI

Uses an antijoin for a table or view on the left and a cube on the right.

CUBE JOIN

ANTI SNA

Uses an antijoin (single-sided null aware) for a table or view on the left and a cube on the right. The join column on the right (cube side) is NOT NULL.

CUBE JOIN

OUTER

Uses an outer join for a table or view on the left and a cube on the right.

CUBE JOIN

RIGHT SEMI

Uses a right semijoin for a table or view on the left and a cube on the right.

CUBE SCAN

Uses inner joins for all cube access.

CUBE SCAN

PARTIAL OUTER

Uses an outer join for at least one dimension, and inner joins for the other dimensions.

CUBE SCAN

OUTER

Uses outer joins for all cube access.

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

GROUP BY

Operation hashing a set of rows into groups for a query with a GROUP BY clause.

HASH

GROUP BY PIVOT

Operation hashing a set of rows into groups for a query with a GROUP BY clause. The PIVOT option indicates a pivot-specific optimization for the HASH GROUP BY operator.

HASH JOIN

(These are join operations.)

Operation joining two sets of rows and returning the result. This join method is useful for joining large data sets of data (DSS, Batch). The join condition is an efficient way of accessing the second table.

Query optimizer uses the smaller of the two tables/data sources to build a hash table on the join key in memory. Then it scans the larger table, probing the hash table to find the joined rows.

HASH JOIN

ANTI

Hash (left) antijoin

HASH JOIN

SEMI

Hash (left) semijoin

HASH JOIN

RIGHT ANTI

Hash right antijoin

HASH JOIN

RIGHT SEMI

Hash right semijoin

HASH JOIN

OUTER

Hash (left) outer join

HASH JOIN

RIGHT OUTER

Hash right outer join

INDEX

(These are access methods.)

UNIQUE SCAN

Retrieval of a single rowid from an index.

INDEX

RANGE SCAN

Retrieval of one or more rowids from an index. Indexed values are scanned in ascending order.

INDEX

RANGE SCAN DESCENDING

Retrieval of one or more rowids from an index. Indexed values are scanned in descending order.

INDEX

FULL SCAN

Retrieval of all rowids from an index when there is no start or stop key. Indexed values are scanned in ascending order.

INDEX

FULL SCAN DESCENDING

Retrieval of all rowids from an index when there is no start or stop key. Indexed values are scanned in descending order.

INDEX

FAST FULL SCAN

Retrieval of all rowids (and column values) using multiblock reads. No sorting order can be defined. Compares to a full table scan on only the indexed columns. Only available with the cost based optimizer.

INDEX

SKIP SCAN

Retrieval of rowids from a concatenated index without using the leading column(s) in the index. Only available with the cost based optimizer.

INLIST ITERATOR

Iterates over the next operation in the plan 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 value, combining each row from one set with the matching rows from the other, and returning the result.

MERGE JOIN

OUTER

Merge join operation to perform an outer join statement.

MERGE JOIN

ANTI

Merge antijoin.

MERGE JOIN

SEMI

Merge semijoin.

MERGE JOIN

CARTESIAN

Can result from 1 or more of the tables not having any join conditions to any other tables in the statement. Can occur even with a join and it may not be flagged as CARTESIAN in the plan.

CONNECT BY

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

MAT_VIEW REWRITE ACCESS

(These are access methods.)

FULL

Retrieval of all rows from a materialized view.

MAT_VIEW REWRITE ACCESS

SAMPLE

Retrieval of sampled rows from a materialized view.

MAT_VIEW REWRITE ACCESS

CLUSTER

Retrieval of rows from a materialized view based on a value of an indexed cluster key.

MAT_VIEW REWRITE ACCESS

HASH

Retrieval of rows from materialized view based on hash cluster key value.

MAT_VIEW REWRITE ACCESS

BY ROWID RANGE

Retrieval of rows from a materialized view based on a rowid range.

MAT_VIEW REWRITE ACCESS

SAMPLE BY ROWID RANGE

Retrieval of sampled rows from a materialized view based on a rowid range.

MAT_VIEW REWRITE ACCESS

BY USER ROWID

If the materialized view rows are located using user-supplied rowids.

MAT_VIEW REWRITE ACCESS

BY INDEX ROWID

If the materialized view is nonpartitioned and rows are located using indexes.

MAT_VIEW REWRITE ACCESS

BY GLOBAL INDEX ROWID

If the materialized view is partitioned and rows are located using only global indexes.

MAT_VIEW REWRITE ACCESS

BY LOCAL INDEX ROWID

If the materialized view 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, and INVALID.

The MAT_VIEW REWRITE 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 REMOVE_LOCATION (MAT_VIEW REWRITE ACCESS only), and INVALID.

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 Database compares each row of the outer set with each row of the inner set, returning rows that satisfy a condition. This join method is useful for joining small subsets of data (OLTP). The join condition is an efficient way of accessing the second table.

NESTED LOOPS

OUTER

Nested loops operation to perform an outer join statement.

PARTITION

Iterates over the next operation in the plan 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 equipartitioned 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 6-2 for valid values of partition start and stop.

PARTITION

SINGLE

Access one partition.

PARTITION

ITERATOR

Access many partitions (a subset).

PARTITION

ALL

Access all partitions.

PARTITION

INLIST

Similar to iterator, but based on an IN-list predicate.

PARTITION

INVALID

Indicates that the partition set to be accessed is empty.

POLYMORPHIC TABLE FUNCTION

Indicates the row source for a polymorphic table function, which is a table function whose return type is determined by its arguments.

PX ITERATOR

BLOCK, CHUNK

Implements the division of an object into block or chunk ranges among a set of parallel execution servers.

PX COORDINATOR

Implements the Query Coordinator which controls, schedules, and executes the parallel plan below it using parallel execution servers. It also represents a serialization point, as the end of the part of the plan executed in parallel and always has a PX SEND QC operation below it.

PX PARTITION

Same semantics as the regular PARTITION operation except that it appears in a parallel plan.

PX RECEIVE

Shows the consumer/receiver parallel execution node reading repartitioned data from a send/producer (QC or parallel execution server) executing on a PX SEND node. This information was formerly displayed into the DISTRIBUTION column. See Table 7-1.

PX SEND

QC (RANDOM), HASH, RANGE

Implements the distribution method taking place between two sets of parallel execution servers. Shows the boundary between two sets and how data is repartitioned on the send/producer side (QC or side. This information was formerly displayed into the DISTRIBUTION column. See Table 7-1.

REMOTE

Retrieval of data from a remote database.

SEQUENCE

Operation involving accessing values of a sequence.

SORT

AGGREGATE

Retrieval of a single row after applying a group function to a set of selected rows. In this case, the database “sorts” a single row.

SORT

UNIQUE

Operation sorting a set of rows to eliminate duplicates.

SORT

GROUP BY

Operation sorting a set of rows into groups for a query with a GROUP BY clause.

SORT

GROUP BY PIVOT

Operation sorting a set of rows into groups for a query with a GROUP BY clause. The PIVOT option indicates a pivot-specific optimization for the SORT GROUP BY operator.

SORT

JOIN

Operation sorting a set of rows before a merge-join.

SORT

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.

TABLE ACCESS

SAMPLE

Retrieval of sampled rows from a table.

TABLE ACCESS

CLUSTER

Retrieval of rows from a table based on a value of an indexed cluster key.

TABLE ACCESS

HASH

Retrieval of rows from table based on hash cluster key value.

TABLE ACCESS

BY ROWID RANGE

Retrieval of rows from a table based on a rowid range.

TABLE ACCESS

SAMPLE BY ROWID RANGE

Retrieval of sampled rows from a table based on a rowid range.

TABLE ACCESS

BY USER ROWID

If the table rows are located using user-supplied rowids.

TABLE ACCESS

BY INDEX ROWID

If the table is nonpartitioned and rows are located using index(es).

TABLE ACCESS

BY GLOBAL INDEX ROWID

If the table is partitioned and rows are located using only global indexes.

TABLE ACCESS

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, and 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 REMOVE_LOCATION (TABLE ACCESS only), and INVALID.

TRANSPOSE

Operation evaluating a PIVOT operation by transposing the results of GROUP BY to produce the final pivoted data.

UNION

Operation accepting two sets of rows and returns the union of the sets, eliminating duplicates.

UNPIVOT

Operation that rotates data from columns into rows.

VIEW

Operation performing a view's query and then returning the resulting rows to another operation.

7.3 DISTRIBUTION Column of PLAN_TABLE

The DISTRIBUTION column indicates the method used to distribute rows from producer query servers to consumer query servers.

Table 7-3 Values of DISTRIBUTION Column of the PLAN_TABLE

DISTRIBUTION Text Description

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 QC 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 QC consumes the input randomly. Used when the statement does not have an ORDER BY clause.