This table and sample data is used in the examples below:

CREATE TABLE table_explain_plan_1
(
    id1 NUMBER(5,0) NOT NULL
,   id2 NUMBER(5,0) NOT NULL
,   type1 NUMBER(1,0) NOT NULL
);

DECLARE
indexLoop NUMBER(5,0);

BEGIN
FOR indexLoop IN 1..50000 LOOP
    IF MOD(indexLoop,2) = 0 THEN
        INSERT INTO table_explain_plan_1 (id1, id2, type1) VALUES (indexLoop, DBMS_RANDOM.VALUE(1,50000), 1);
    ELSE
        INSERT INTO table_explain_plan_1 (id1, id2, type1) VALUES (indexLoop, DBMS_RANDOM.VALUE(1,50000), 2);
    END IF;
END LOOP;
END;
/

COMMIT;

CREATE INDEX ix_table_explain_plan_1_type1 ON table_explain_plan_1 (type1);
ANALYZE INDEX ix_table_explain_plan_1_type1 COMPUTE STATISTICS;

The table contains 50,000 rows with an even distribution of type1 values (1 and 2), making it ideal for demonstrating execution plan behavior.

The index on type1 column is important for Oracle's cost-based optimizer to make informed decisions about query execution paths.

The estimated execution plan of a query provides a detailed roadmap of how Oracle's cost-based optimizer intends to execute a SQL statement. This plan is generated before query execution and represents Oracle's best estimate based on available table and index statistics. The estimated execution plan takes into account the statistics available at the time it is generated, including table cardinality, column selectivity, index statistics, and system resources. These statistics are important for the optimizer to calculate costs and choose the most efficient access paths. Execution plans are essential for performance tuning as they reveal potential bottlenecks such as full table scans on large tables, inefficient join methods, or missing indexes. They can be used to debug queries that take a long time to execute and to verify that SQL modifications produce the expected optimization improvements.

1. PLAN_TABLE Architecture and Storage
   Oracle stores estimated execution plans in a special table called PLAN_TABLE.
   

   SELECT object_type, owner FROM dba_objects WHERE object_name = 'PLAN_TABLE';
   

   Query result:

   | OBJECT_TYPE  | OWNER  |
   +--------------+--------+
   | SYNONYM      | PUBLIC |
   

   PLAN_TABLE is actually a public synonym that points to the underlying PLAN_TABLE$ table owned by SYS:
   

   SELECT table_name, table_owner, owner FROM dba_synonyms WHERE synonym_name = 'PLAN_TABLE';
   

   Query result:

   | table_name  | table_owner | owner  |
   +-------------+-------------+--------|
   | PLAN_TABLE$ | SYS         | PUBLIC |
   

   The PLAN_TABLE$ is implemented as a Global Temporary Table (GTT) with session-specific storage, meaning each session maintains its own execution plan data that is automatically cleaned up when the session ends:
   

   SELECT temporary, duration FROM dba_tables WHERE table_name = 'PLAN_TABLE$';
   

   Query result:

   | temporary | duration    |
   +-----------+-------------+
   | Y         | SYS$SESSION |
   

   The complete DDL for creating the PLAN_TABLE$ can be found in the "catplan.sql" script located in the "$ORACLE_HOME/rdbms/admin/" directory. This table structure has evolved across Oracle versions to support new optimizer features and execution plan attributes.

   create global temporary table plan_table$
   (
       statement_id       varchar2(30),
       plan_id            number,
       timestamp          date,
       remarks            varchar2(4000),
       operation          varchar2(30),
       options            varchar2(255),
       object_node        varchar2(128),
       object_owner       varchar2(30),
       object_name        varchar2(30),
       object_alias       varchar2(65),
       object_instance    numeric,
       object_type        varchar2(30),
       optimizer          varchar2(255),
       search_columns     number,
       id                 numeric,
       parent_id          numeric,
       depth              numeric,
       position           numeric,
       cost               numeric,
       cardinality        numeric,
       bytes              numeric,
       other_tag          varchar2(255),
       partition_start    varchar2(255),
       partition_stop     varchar2(255),
       partition_id       numeric,
       other              long,
       other_xml          clob,
       distribution       varchar2(30),
       cpu_cost           numeric,
       io_cost            numeric,
       temp_space         numeric,
       access_predicates  varchar2(4000),
       filter_predicates  varchar2(4000),
       projection         varchar2(4000),
       time               numeric,
       qblock_name        varchar2(30)
   ) on commit preserve rows
   /
   

2. Key Columns in PLAN_TABLE
   The PLAN_TABLE contains numerous columns that provide detailed information about each step in the execution plan. The most important columns include:
   

   • ID and PARENT_ID: These columns establish the hierarchical relationship between execution plan steps, where each step references its parent operation.
     
   • OPERATION and OPTIONS: Describe the type of database operation (TABLE ACCESS, INDEX SCAN, etc.) and specific method (FULL, BY INDEX ROWID, RANGE SCAN, etc.).
     
   • OBJECT_NAME and OBJECT_OWNER: Identify the database objects (tables, indexes, views) involved in each operation.
     
   • COST, CARDINALITY, and BYTES: Provide optimizer estimates for resource consumption, number of rows, and data volume for each operation.
     
   • ACCESS_PREDICATES and FILTER_PREDICATES: Show the WHERE clause conditions applied at each step, distinguishing between access predicates (used for index lookups) and filter predicates (applied after data retrieval).
     
   • CPU_COST, IO_COST, and TIME: Break down the estimated costs into CPU and I/O components, with time estimates for each operation.
     

The EXPLAIN PLAN command is the most commonly used method for generating estimated execution plans. It populates the PLAN_TABLE with execution plan data without actually executing the SQL statement, making it safe for use in production environments.

The basic syntax requires the FOR keyword followed by the SQL statement to be analyzed:

EXPLAIN PLAN
FOR
SELECT * FROM table_explain_plan_1 WHERE type1 = 1;

After running EXPLAIN PLAN, you can retrieve the formatted execution plan using the DBMS_XPLAN.DISPLAY function, which provides a readable, hierarchical representation of the execution steps:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 3358088968

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                               | 24721 |   941K|    50   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_EXPLAIN_PLAN_1          | 24721 |   941K|    50   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TABLE_EXPLAIN_PLAN_1_TYPE1 |   208 |       |    49   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("TYPE1"=1)

The execution plan shows that Oracle chose to use an index range scan followed by table access by rowid, which is typically efficient for selective queries. The asterisk (*) next to operation ID 2 indicates that predicate information is available for that step.

The AUTOTRACE command is a SQL*Plus and SQL Developer feature that provides a convenient way to generate execution plans without requiring separate queries to retrieve the plan information. Unlike EXPLAIN PLAN, AUTOTRACE can also show actual execution statistics when the query is run.

AUTOTRACE offers several modes of operation. The TRACEONLY EXPLAIN option generates only the estimated execution plan without executing the statement:

SET AUTOTRACE TRACEONLY EXPLAIN
SELECT * FROM table_explain_plan_1 WHERE type1 = 1
/

Plan hash value: 3358088968

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                               | 24721 |   941K|    50   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_EXPLAIN_PLAN_1          | 24721 |   941K|    50   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TABLE_EXPLAIN_PLAN_1_TYPE1 |   208 |       |    49   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("TYPE1"=1)

Additional AUTOTRACE options include TRACEONLY STATISTICS (shows execution statistics without the plan) and ON (displays query results, execution plan, and statistics). The AUTOTRACE command automatically formats the output and doesn't require manual querying of PLAN_TABLE, making it convenient for interactive performance analysis sessions.

For advanced analysis and custom reporting, you can query the PLAN_TABLE directly. This approach provides access to all execution plan columns and allows for custom formatting and filtering of plan information.
When using EXPLAIN PLAN with a custom STATEMENT_ID, you can organize and retrieve specific execution plans from multiple analyzed statements:

EXPLAIN PLAN
SET STATEMENT_ID = 'table_explain_plan_1_1'
FOR
SELECT * FROM table_explain_plan_1 WHERE type1 = 1;

The following query demonstrates how to construct a hierarchical view of the execution plan using Oracle's CONNECT BY syntax. This approach replicates the tree structure shown by DBMS_XPLAN.DISPLAY:

SELECT
    id
,   parent_id
,   cardinality AS "ROWS"
,   lpad(' ',level-1)||operation||' '||options||' '||object_name AS "STEP"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id AND prior statement_id = statement_id
START WITH id = 0 AND statement_id = 'table_explain_plan_1_1'
ORDER BY id;

Query result:

| id | parent_id | ROWS  | STEP                                              |
+----+-----------+-------+---------------------------------------------------|
| 0  |           | 24721 | SELECT STATEMENT                                  |
| 1  | 0         | 24721 |  TABLE ACCESS BY INDEX ROWID table_explain_plan_1 |
| 2  | 1         | 208   |   INDEX RANGE SCAN ix_table_explain_plan_1_type1  |

This direct query approach allows you to access additional columns not displayed by standard formatting tools, such as CPU_COST, IO_COST, ACCESS_PREDICATES, and FILTER_PREDICATES, which can be valuable for detailed performance analysis and optimization efforts.