Search notes:

Oracle: PLAN_TABLE

PLAN_TABLE is the name of a table that is used by the SQL statement explain plan to write its result to.
Such a plan table can be created by executing $ORACLE_HOME/rdbms/admin/catplan.sql. Among others, this script creates a global temporary table named PLAN_TABLE$ (which belongs to SYS) and a public synonym, PLAN_TABLE, that points to the table.
A plan table can also be created by just executing the following create table statement:
create table PLAN_TABLE /* sharing=none */  (
        statement_id       varchar2(30)  ,  -- The value that was given to the (optional) clause SET STATEMENT_ID in an explain plan statement
        plan_id            number        ,  -- Uniquely identifies a plan (not a record). The value is taken from the sequence SYS.ORA_PLAN_ID_SEQ$,
        timestamp          date          ,
        remarks            varchar2(4000),
        operation          varchar2(30)  ,  -- Records the plan operation for this step except if id = 0 (first row in plan).
                                            -- For the first row, operation contains the SQL statement type: DELETE STATEMENT, MERGE STATEMENT, SELECT STATEMENT, INSERT STATEMENT,
                                            -- CREATE TABLE STATEMENT or CREATE INDEX STATEMENT
        options            varchar2(255) ,  -- Some operations have different execution options, for example the operation «INDEX» can be run
                                            -- with option «FULL SCAN», «FAST FULL SCAN», «RANGE SCAN» etc.
        object_node        varchar2(128) ,  -- Name of database link or for parallel queries order in which output from operation is consumed
        object_owner       varchar2(128) ,
        object_name        varchar2(128) ,  -- Identifies the name of the object that is being accessed in the respective operation
        object_alias       varchar2(261) ,
        object_instance    numeric       ,  -- Ordinal position of object as found in SQL statement (left to right, outer to inner).
                                            -- View expansion number is unpredictable.
        object_type        varchar2(30)  ,
        optimizer          varchar2(255) ,  -- In first row (statement line) in a plan: optimizer mode (ALL_ROWS, CHOOSE … ).
                                            -- For other rows, the value ANALYZED indicates that the accessed object is analyzied
        search_columns     number        ,  -- Number of index columns that can be used to match a predicate,
                                            -- that is: the number of index columns with start and stop keys.
        id                 numeric       ,  -- Unique id for each record in given plan. Use this value to order the records of a plan with order by.
        parent_id          numeric       ,
        depth              numeric       ,
        position           numeric       ,  -- First row in plan_table (id = 0): estimated cost (and equal to value in cost, see also here).
                                            -- Other rows: relative position of children with same parent
        cost               numeric       ,  -- cost = F(cpu_cust, io_cost). A cost of 1 is (apparently) equal to the time required to read a single block.
        cardinality        numeric       ,
        bytes              numeric       ,
        other_tag          varchar2(255) ,  -- NULL, SERIAL(?), SERIAL_FROM_REMOTE, PARALLEL_FROM_SERIAL, PARALLEL_TO_SERIAL, PARALLEL_TO_PARALLEL, PARALLEL_COMBINED_WITH_PARENT or PARALLEL_COMBINED_WITH_CHILD, SINGLE_COMBINED_WITH_CHILD, SINGLE_COMBINED_WITH_PARENT
        partition_start    varchar2(255) ,  -- PARTITION_START and PARTITION_STOP are used in combination
        partition_stop     varchar2(255) ,  -- with partition pruning.
        partition_id       numeric       ,
        other              long          ,  -- Text with select statements. For the plan operation remote, OTHER contains the SQL statement that is executed at the remote site.
        distribution       varchar2(30)  ,  -- How rows are distributed from a producer query server to consumer query servers.
        cpu_cost           numeric       ,
        io_cost            numeric       ,
        temp_space         numeric       ,  -- Estimated required temporary space for the operation, for example sort or hash join.
        access_predicates  varchar2(4000),  -- Predicates that are used to locate rows, for example as start/stop predicates in an index range scan.
                                            -- Only operations where access_predicates is filled seem to be INDEX, CONNECT BY, HASH JOIN and SORT
        filter_predicates  varchar2(4000),  -- Predicates that are used to filter rows. compare with the plan operation FILTER
        projection         varchar2(4000),
        time               numeric       ,  -- Estimated duration (in seconds) that SQL statements spends for this operation
        qblock_name        varchar2(128) ,  -- Name of query block (either generated or specified with the qb_name hint)
        other_xml          clob             -- Additional, differently structured (hence XML), values for an execution step.
);

See also

This SQL statement creates a matrix of plan operations and columns in plan_table and can be used to which operators fill which column.
plan_table does not have %plan_hash_value column(s).
awr_cdb_sql_plan, awr_pdb_sql_plan, awr_root_sql_plan, dba_sqlset_plans, dba_advisor_sql_plan, dba_hist_sql_plan, data_pump_xpl_table$, dba_sqltune_plans, v$advisor_current_sqlplan, v$all_sql_plan, v$sql_plan, v$sql_plan_statistics_all, sqlobj$plan, sqlobj$plan_datapump, sqlobj$plan_datapump_tbl, system.mview$_adv_plan, wrh$_sql_plan, wrhs$_sql_plan, wri$_adv_sqlt_plans, wri$_sqlset_plan_lines, wri$_sqlset_workspace_plans
Object types related to the plan table.

Index