PLAN_TABLE is the default sample output table into which the EXPLAIN
PLAN
statement inserts rows describing execution plans. This post summarizes the metadata information of this table for Oracle8/8i, 9i, 10g, and 11g; read post, to know the methods for obtaining the formatted explain plan output from plan_table.
Column | Datatype | Description | Notes |
STATEMENT_ID | VARCHAR2(30) | Value of the optional STATEMENT_ID parameter specified in the EXPLAIN PLAN statement. | Available from 8i |
PLAN_ID | NUMBER | Unique identifier of a plan in the database. | Added in 9i |
TIMESTAMP | DATE | Date and time when the EXPLAIN PLAN statement was generated. | Available from 8i |
REMARKS | VARCHAR2(80) | Any comment (of up to 80 bytes) you want to associate with each step of the explained plan. This column is used to indicate whether an outline or SQL Profile was used 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. | Available from 8i |
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, INSERT, UPDATE, SELECT. | Available from 8i |
OPTIONS | VARCHAR2(225) | A variation on the operation described in the OPERATION column. | Available from 8i |
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 output from operations is consumed. | Available from 8i |
OBJECT_OWNER | VARCHAR2(30) | Name of the user who owns the schema containing the table or index. | Available from 8i |
OBJECT_NAME | VARCHAR2(30) | Name of the table or index. | Available from 8i |
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. | Added in 10g |
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 with respect to the original statement text. View expansion results in unpredictable numbers. | Available from 8i |
OBJECT_TYPE | VARCHAR2(30) | Modifier that provides descriptive information about the object; for example, NON-UNIQUE for indexes. | Available from 8i |
OPTIMIZER | VARCHAR2(255) | Current mode of the optimizer. | Available from 8i |
SEARCH_COLUMNS | NUMERIC | Not currently used. | Available from 8i |
ID | NUMERIC | A number assigned to each step in the execution plan. | Available from 8i |
PARENT_ID | NUMERIC | The ID of the next execution step that operates on the output of the ID step. | Available from 8i |
DEPTH | NUMERIC | Depth of the operation in the row source tree that the plan represents. The value can be used for indenting the rows in a plan table report. | Added in 10g |
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. | Available from 8i |
COST | NUMERIC | Cost of the operation as estimated by the optimizer’s query approach. 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. | Available from 8i |
CARDINALITY | NUMERIC | Estimate by the query optimization approach of the number of rows accessed by the operation. | Available from 8i |
BYTES | NUMERIC | Estimate by the query optimization approach of the number of bytes accessed by the operation. | Available from 8i |
OTHER_TAG | VARCHAR2(255) | Describes the contents of the OTHER column. | Available from 8i |
PARTITION_START | VARCHAR2(255) | Start partition of a range of accessed partitions. | Available from 8i |
PARTITION_STOP | VARCHAR2(255) | Stop partition of a range of accessed partitions. | Available from 8i |
PARTITION_ID | NUMERIC | Step that has computed the pair of values of the PARTITION_START and PARTITION_STOP columns. | Available from 8i |
OTHER | LONG | Other information that is specific to the execution step that a user might find useful. See the OTHER_TAG column. | Available from 8i |
DISTRIBUTION | VARCHAR2(30) | Method used to distribute rows from producer query servers to consumer query servers. | Available from 8i |
CPU_COST | NUMERIC | CPU cost of the operation as estimated by the query optimizer’s approach. 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. | Added in 9i |
IO_COST | NUMERIC | I/O cost of the operation as estimated by the query optimizer’s approach. 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. | Added in 9i |
TEMP_SPACE | NUMERIC | Temporary space, in bytes, used by the operation as estimated by the query optimizer’s approach. For statements that use the rule-based approach, or for operations that do not use any temporary space, this column is null. | Added in 10g |
ACCESS_PREDICATES | VARCHAR2(4000) | Predicates used to locate rows in an access structure. For example, start or stop predicates for an index range scan. | Added in 9i |
FILTER_PREDICATES | VARCHAR2(4000) | Predicates used to filter rows before producing them. | Added in 10g |
PROJECTION | VARCHAR2(4000) | Expressions produced by the operation. | Added in 10g |
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. | Added in 10g |
QBLOCK_NAME | VARCHAR2(30) | Name of the query block, either system-generated or defined by the user with the QB_NAME hint. | Added in 10g |