PLAN TABLE in Oracle

Oracle Performance Series

PLAN_TABLEWhat EXPLAIN PLAN stores, how DBMS_XPLAN reads it, and where plan analysis goes wrong

PLAN_TABLE is the staging area Oracle uses for EXPLAIN PLAN output. That makes it foundational, but also easy to misuse. A plan stored in PLAN_TABLE shows what the optimizer estimated for the explained statement. It does not prove what a live cursor actually did at runtime. To use it well, you need to understand the table’s role, how DBMS_XPLAN formats it, how to read the tree, and when to move from explained plans to cursor-based evidence.

EXPLAIN PLANWrites plan rows here

DBMS_XPLANFormats and displays

Estimated, not executedCritical distinction

Cursor stats matterFor runtime truth

Table of contents

01. Mental modelWhat PLAN_TABLE is and is not02. AnatomyThe columns that matter most03. Explain-plan workflowHow statements land in PLAN_TABLE04. DBMS_XPLAN formatsDISPLAY, DISPLAY_CURSOR, and output choices05. Reading the treeHow to interpret parent-child plan steps06. Direct queries and maintenanceQuerying rows and keeping plans organized07. Limits and pitfallsWhere PLAN_TABLE can mislead you08. Lab and quizPractical workflow for plan analysis

Section 01

Start with the right mental model: PLAN_TABLE is an explain-plan repository

PLAN_TABLE is the default output table for the EXPLAIN PLAN statement, and DBMS_XPLAN.DISPLAY reads from it by default. In modern Oracle releases, Oracle supplies the infrastructure automatically, and scripts such as utlxplan.sql and catplan.sql still matter when creating plan tables or public synonyms manually.

EXPLAIN PLAN writes rowsDISPLAY reads them backNot a runtime proof

Critical distinction

An explained plan is not the same thing as an executed cursor plan. Oracle’s own documentation warns that explained plans can differ from the plan actually used at runtime, especially when bind peeking, adaptive behavior, or other runtime conditions matter.

Bottom line: PLAN_TABLE is a useful plan repository, but it is only one part of Oracle plan analysis.

Section 02

PLAN_TABLE anatomy: which columns matter most

PLAN_TABLE contains many columns, but only a subset drives most tuning conversations. The table includes identifiers for plan hierarchy, estimated row counts, bytes, costs, predicates, and extended metadata stored in XML.

Column	What it helps you understand
ID	The step number inside the plan tree.
PARENT_ID	The parent-child relationship between operations.
POSITION	Useful for ordering and reading siblings in the tree.
OPERATION	The main action, such as TABLE ACCESS, INDEX RANGE SCAN, or HASH JOIN.
OPTIONS	The subtype, such as FULL, UNIQUE SCAN, or BY INDEX ROWID.
OBJECT_NAME	The table, index, or other object involved in the step.
COST	The optimizer’s relative cost estimate for the operation or statement.
CARDINALITY	The estimated number of rows for the step.
BYTES	The estimated number of bytes produced.
OTHER_XML	Extra structured metadata used by Oracle tools and richer plan displays.

What matters first

For most tuning work, start with ID, PARENT_ID, OPERATION, OPTIONS, OBJECT_NAME, CARDINALITY, and COST. Then add predicates, notes, and runtime row-source stats when needed.

SQL - Inspect PLAN_TABLE structure

DESC plan_table;

-- Or inspect selected columns explicitly
SELECT column_name, data_type, data_length
FROM   user_tab_columns
WHERE  table_name = 'PLAN_TABLE'
ORDER BY column_id;

Section 03

How rows get into PLAN_TABLE

The normal workflow is straightforward: you issue EXPLAIN PLAN FOR ..., Oracle inserts plan rows into PLAN_TABLE, and then you display them with DBMS_XPLAN.DISPLAY. If you are comparing multiple statements, STATEMENT_ID helps you keep the plan rows separated.

1. Explain the statement

Oracle estimates a plan and writes rows into PLAN_TABLE.

2. Tag if needed

Use STATEMENT_ID when comparing more than one explained statement.

3. Display the rows

DBMS_XPLAN.DISPLAY formats the stored rows into readable output.

4. Clean up

Delete old rows when you no longer need them for that analysis cycle.

SQL - Basic explain-plan workflow

EXPLAIN PLAN FOR
SELECT e.last_name, d.department_name, e.salary
FROM   employees e
       JOIN departments d
         ON e.department_id = d.department_id
WHERE  e.salary > 10000
ORDER BY e.salary DESC;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

SQL - Use STATEMENT_ID for cleaner comparisons

EXPLAIN PLAN SET statement_id = 'EMP50' FOR
SELECT *
FROM   employees
WHERE  department_id = 50;

SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY(
           table_name   => 'PLAN_TABLE',
           statement_id => 'EMP50',
           format       => 'TYPICAL'
         )
       );

Section 04

DBMS_XPLAN formats: what DISPLAY tells you, and what DISPLAY_CURSOR adds

DBMS_XPLAN.DISPLAY reads plan rows from PLAN_TABLE. DBMS_XPLAN.DISPLAY_CURSOR reads execution-plan data for statements already in the library cache. That distinction is one of the most important in Oracle plan analysis because cursor-based displays can include runtime row-source statistics, while explained plans cannot.

DISPLAY

Formats rows already stored in PLAN_TABLE, typically after EXPLAIN PLAN.

DISPLAY_CURSOR

Formats the cursor plan from the shared SQL area and can show runtime statistics such as ALLSTATS LAST.

SQL - Common DBMS_XPLAN format patterns

-- Minimal output
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC'));

-- Default style
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));

-- Richer output with predicates and notes
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));

-- Runtime stats from an executed cursor
SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY_CURSOR(
           format => 'ALLSTATS LAST'
         )
       );

Reading tip

When estimated rows and actual rows diverge sharply in a cursor-based display, that is often a signal to review statistics, predicates, skew, or cardinality assumptions rather than merely staring at the plan shape.

Section 05

How to read the plan tree without getting lost

A plan is a tree, not a flat checklist. ID, PARENT_ID, indentation, and sibling order tell you which steps feed which others. The safest habit is to see each row as a row source that produces data for its parent. A scan or access operation produces rows, a join combines them, and a sort or aggregate consumes the incoming stream higher up the tree.

Sample plan shape

-- Id  Operation                          Name              Rows  Cost
--  0  SELECT STATEMENT                                      5    12
--  1   SORT ORDER BY                                        5    12
--  2    NESTED LOOPS                                        5    11
--  3     TABLE ACCESS BY INDEX ROWID EMPLOYEES              5     4
--  4      INDEX RANGE SCAN          IDX_EMP_DEPT            5     2
--  5     TABLE ACCESS BY INDEX ROWID DEPARTMENTS            1     1
--  6      INDEX UNIQUE SCAN         DEPT_ID_PK              1     0

1. Find the statement root

The top SELECT STATEMENT is the overall consumer of the plan tree.

2. Trace row sources upward

Step 4 feeds step 3, and step 6 feeds step 5. Those outputs then feed the join at step 2.

3. Read operations with context

An index scan by itself is not the whole story. Ask what table access, join, or sort it feeds.

4. Compare estimates carefully

Cost and cardinality estimates are more useful when read together than in isolation.

Bottom line: Read a plan as a flow of row sources feeding parent operations, not as unrelated lines of text.

Section 06

Querying PLAN_TABLE directly and keeping it organized

DBMS_XPLAN is the preferred display layer, but direct queries against PLAN_TABLE are still useful when you want custom reporting, hierarchical views, or cleanup by STATEMENT_ID. This is especially handy during iterative tuning sessions.

SQL - Simple hierarchical query against PLAN_TABLE

SELECT LPAD(' ', 2 * LEVEL) ||
       operation || ' ' ||
       NVL(options, '') || ' ' ||
       NVL(object_name, '') AS plan_step,
       cost,
       cardinality AS est_rows
FROM   plan_table
WHERE  statement_id = 'EMP50'
CONNECT BY PRIOR id = parent_id
START WITH id = 0
ORDER SIBLINGS BY id;

SQL - Remove old explained rows for a statement

DELETE FROM plan_table
WHERE  statement_id = 'EMP50';

COMMIT;

Operational habit

If you explain many similar statements during tuning, disciplined use of STATEMENT_ID makes both cleanup and comparison much easier.

Section 07

Where PLAN_TABLE can mislead you

PLAN_TABLE is useful, but it has limits. Oracle’s own documentation distinguishes explained plans from plans already associated with executed cursors. That distinction matters because runtime decisions, bind handling, adaptive behavior, and actual row-source statistics are not fully represented in a basic explained plan.

Potential mistake	Better interpretation
Treating EXPLAIN PLAN as proof of runtime behavior	Use it as an estimate. Move to DISPLAY_CURSOR or other runtime evidence when execution truth matters.
Reading cost as elapsed time	Cost is a relative optimizer estimate, not a wall-clock measurement.
Ignoring predicates, notes, and cardinality	The operation name alone rarely tells the whole story.
Comparing explained plans without isolating STATEMENT_ID	Mixed rows in PLAN_TABLE can make analysis messy or misleading.
Stopping at plan shape	Always ask whether estimates aligned with actual row-source behavior on the executed cursor.

Estimate nuance

An explained plan is often a useful first pass, but it is still an estimate.

Runtime nuance

Cursor-based plans let you compare estimated and actual behavior more responsibly.

Workflow nuance

The healthiest tuning workflow moves from explained plan to runtime validation when needed.

Common trap

It is easy to become too confident because DBMS_XPLAN.DISPLAY looks polished. The formatting is polished; the underlying rows are still only as reliable as the explained estimate behind them.

Section 08

Hands-on workflow: explain, display, validate, then quiz yourself

This workflow is a practical way to keep explained plans and runtime plans in the right order. Start with EXPLAIN PLAN when you want a quick optimizer estimate. Then move to cursor-based evidence if the statement is worth deeper tuning.

SQL - A compact plan-analysis workflow

-- 1. Explain and tag the statement
EXPLAIN PLAN SET statement_id = 'EMP_SAL' FOR
SELECT employee_id, salary
FROM   employees
WHERE  salary > 10000
ORDER BY salary DESC;

-- 2. Display the explained plan
SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY(
           statement_id => 'EMP_SAL',
           format       => 'ALL'
         )
       );

-- 3. Run the statement with runtime stats if deeper validation is needed
ALTER SESSION SET statistics_level = ALL;

SELECT /*+ gather_plan_statistics */ employee_id, salary
FROM   employees
WHERE  salary > 10000
ORDER BY salary DESC;

-- 4. Compare runtime evidence
SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY_CURSOR(
           NULL,
           NULL,
           'ALLSTATS LAST +PREDICATE +BYTES +COST'
         )
       );

Good workflow

Use explained plans for a quick estimate, then step up to cursor evidence when the statement matters.

Weak workflow

Treat a clean-looking explained plan as final proof and stop there.

Answer from sound Oracle plan-analysis practice

Q1. What does DBMS_XPLAN.DISPLAY read by default?

The currently executing cursor only.

PLAN_TABLE rows.

Only SQL Monitor output.

The alert log.

Correct answer: PLAN_TABLE.

Q2. What is the safest summary of an explained plan?

It proves exactly what happened at runtime.

It is identical to DISPLAY_CURSOR.

It shows an optimizer estimate for the explained statement, not guaranteed runtime truth.

It is useful only for historical plans.

Correct answer: an explained plan is an estimate.

Q3. Why is STATEMENT_ID useful?

It keeps explained plan rows organized when multiple statements are being compared.

It forces Oracle to use a different optimizer.

It changes runtime row counts.

It removes the need for DBMS_XPLAN.

Correct answer: organization and isolation.

Q4. When do you usually move from DISPLAY to DISPLAY_CURSOR?

Never. DISPLAY is enough for all tuning.

Only when the SQL text is short.

Only after deleting PLAN_TABLE.

When you need runtime evidence, such as estimated versus actual rows.

Correct answer: move to cursor evidence when runtime truth matters.

Takeaways

PLAN_TABLE matters because it supports EXPLAIN PLAN workflows and quick plan inspection. But the strongest plan-analysis habit is to treat explained plans as estimates, use DBMS_XPLAN deliberately, and move to cursor-based evidence when a tuning decision depends on runtime truth.