Deep Dive — DBMS_XPLAN, Reading the Plan That Actually Ran

Answers one question: what did the optimizer do with my SQL — and where does the plan disagree with reality.

Used in: Chapter 10 — Measure
Appendix entry: DBMS_XPLAN
Pairs with: DBMS_HPROF deep dive (HPROF says which SQL is heavy; XPLAN says why)

1. The Functions — and Which One to Use

Function	Source of the plan	Actual row counts?	Use when
`DISPLAY_CURSOR(sql_id, child_no, format)`	Shared pool (`V$SQL_PLAN`) — what really ran	Yes, with `ALLSTATS`	The SQL has already run. Default choice.
`DISPLAY(table, stmt_id, format)`	`PLAN_TABLE` after `EXPLAIN PLAN FOR`	No	The SQL cannot be run (too slow, DML you don't want to execute). Estimates only.
`DISPLAY_AWR(sql_id, phv, db_id, format)`	AWR history (`DBA_HIST_SQL_PLAN`)	No	The cursor has aged out of the shared pool; "the plan changed last week". Needs Diagnostics Pack.
`DISPLAY_SQL_PLAN_BASELINE(handle, name)`	SQL Plan Management	No	You are pinning plans with SPM.
`DISPLAY_SQLSET(set, sql_id)`	SQL Tuning Set	No	Plans captured into an STS.
`COMPARE_PLANS(ref, list)` (19c+)	Any two of the above	—	Diff two plans side by side.

Rule: EXPLAIN PLAN guesses; DISPLAY_CURSOR reports. EXPLAIN PLAN does not peek binds, treats every bind as VARCHAR2, and ignores adaptive features. If the SQL has run — always DISPLAY_CURSOR.

2. The Format String

The third argument. Base levels plus +/- modifiers:

Level / modifier	Adds
`BASIC`	operation, options, object name
`TYPICAL` (default)	+ E-Rows, Bytes, Cost, Time, predicates, partition and parallel columns
`ALL`	+ projection, aliases, remote SQL
`ADVANCED`	+ outline (the full hint set), peeked binds
`ALLSTATS`	= `IOSTATS MEMSTATS`: A-Rows, A-Time, Starts, Buffers, Reads, memory
`LAST`	statistics of the last execution only (otherwise cumulative since the cursor was loaded)
`+PEEKED_BINDS`	the bind values the optimizer saw when it built the plan
`+OUTLINE`	the hints that reproduce this plan
`+ADAPTIVE` (12c+)	inactive subplan rows of an adaptive plan
`-BYTES -COST`	drop noise columns

The working defaults:

SQL

'ALLSTATS LAST'                  -- 90% of the time
'ALLSTATS LAST +PEEKED_BINDS'    -- "why did it choose this plan for these binds"
'BASIC +PREDICATE'               -- compact: shape + predicates only
'ADVANCED'                       -- when you need the outline to pin a plan

ALLSTATS shows actuals only if they were collected. Either add /*+ GATHER_PLAN_STATISTICS */ to the statement, or ALTER SESSION SET statistics_level = ALL (heavier — every statement in the session). Without one of these you get E-Rows only and a note "basic plan statistics not available".

3. Full Example on the Course Schema

3a. Partition pruning — the Chapter 6 bug, seen in the plan

SQL

SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*), SUM(amount)
FROM   orders
WHERE  EXTRACT(YEAR FROM order_date) = 2024
AND    status = 'NEW';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

sql_id => NULL = the last statement this session ran. In SQL*Plus/SQLcl: SET SERVEROUTPUT OFF first, otherwise the "last statement" is the DBMS_OUTPUT.GET_LINES call.

SQL_ID  9k2r7c3x1b4dq, child number 0
-------------------------------------
Plan hash value: 1735962731

----------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |      1 |        |       |       |      1 |00:04:12.55 |    3812K|
|   1 |  SORT AGGREGATE       |        |      1 |      1 |       |       |      1 |00:04:12.55 |    3812K|
|   2 |   PARTITION RANGE ALL |        |      1 |   5000K|     1 |    72 |     41M|00:04:09.80 |    3812K|
|*  3 |    TABLE ACCESS FULL  | ORDERS |     72 |   5000K|     1 |    72 |     41M|00:04:01.12 |    3812K|
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter((EXTRACT(YEAR FROM INTERNAL_FUNCTION("ORDER_DATE"))=2024 AND "STATUS"='NEW'))

Reading it:

PARTITION RANGE ALL, Pstart 1 / Pstop 72 — all 72 monthly partitions scanned for one year of data.
The predicate is a filter, wrapped in a function — the optimizer cannot map it to partitions.
Buffers 3812K — 3.8M logical reads for what should be 12 partitions.

The fix from Chapter 6, and its plan:

SQL

SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*), SUM(amount)
FROM   orders
WHERE  order_date >= DATE '2024-01-01' AND order_date < DATE '2025-01-01'
AND    status = 'NEW';

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name   | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |        |      1 |        |       |       |      1 |00:00:38.21 |     641K|
|   1 |  SORT AGGREGATE           |        |      1 |      1 |       |       |      1 |00:00:38.21 |     641K|
|   2 |   PARTITION RANGE ITERATOR|        |      1 |     40M|    61 |    72 |     41M|00:00:36.90 |     641K|
|*  3 |    TABLE ACCESS FULL      | ORDERS |     12 |     40M|    61 |    72 |     41M|00:00:35.02 |     641K|
--------------------------------------------------------------------------------------------------------------
   3 - filter("STATUS"='NEW')

ITERATOR, Pstart 61 / Pstop 72, Starts 12, Buffers ÷ 6. Same result, one-sixth of the work. The order_date predicate has disappeared from the filter — it became partition selection.

3b. A cardinality misestimate — E-Rows vs A-Rows and `Starts`

SQL

SELECT /*+ GATHER_PLAN_STATISTICS */ c.customer_id, SUM(o.amount)
FROM   orders o
JOIN   customers c ON c.customer_id = o.customer_id
WHERE  c.tier = 'VIP'
AND    o.order_date >= DATE '2024-12-01' AND o.order_date < DATE '2025-01-01'
GROUP  BY c.customer_id;

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |    498K|00:11:40.03 |    9871K|
|   1 |  HASH GROUP BY                        |                |      1 |     50 |    498K|00:11:40.03 |    9871K|
|   2 |   NESTED LOOPS                        |                |      1 |   4200 |     38M|00:11:21.71 |    9871K|
|   3 |    NESTED LOOPS                       |                |      1 |   4200 |     38M|00:03:12.44 |    2140K|
|*  4 |     TABLE ACCESS FULL                 | CUSTOMERS      |      1 |     50 |    500K|00:00:01.92 |   41203 |
|   5 |     PARTITION RANGE SINGLE            |                |    500K|        |     38M|00:03:08.10 |    2099K|
|*  6 |      INDEX RANGE SCAN                 | ORDERS_CUST_IX |    500K|     84 |     38M|00:02:55.62 |    2099K|
|   7 |    TABLE ACCESS BY LOCAL INDEX ROWID  | ORDERS         |     38M|     84 |     38M|00:08:02.17 |    7731K|
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("C"."TIER"='VIP')
   6 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID")
       filter(("O"."ORDER_DATE">=TO_DATE(' 2024-12-01 00:00:00', ...) AND "O"."ORDER_DATE"<TO_DATE(...)))

Reading it:

Step 4: E-Rows 50, A-Rows 500K — a 10,000x misestimate. The optimizer thinks VIP is rare; it is 10% of 5M customers.
Because it expected 50 outer rows, it chose NESTED LOOPS. Step 6 then ran 500K times (Starts), producing 38M index probes and 38M single-row table accesses (step 7: 8 minutes on its own).
Step 6 itself is estimated correctly: E-Rows 84 × Starts 500K ≈ A-Rows 38M. E-Rows is per start; A-Rows is the total. Always compare E-Rows × Starts with A-Rows.
The cause is not the SQL — it is statistics: no histogram on customers.tier, so the optimizer assumes uniform distribution.

The fix is not a hint:

SQL

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMERS',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS tier SIZE 254');
END;
/

After it, the same SQL gets HASH JOIN with a full scan of the 12/2024 partition — 38 seconds instead of 11 minutes, and E-Rows ≈ A-Rows on every line.

3c. Bind peeking — "it was fast yesterday"

SQL

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('9k2r7c3x1b4dq', NULL, 'ALLSTATS LAST +PEEKED_BINDS'));

Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (VARCHAR2(30), CSID=873): 'CANCELLED'

The plan was built for status = 'CANCELLED' (0.1% of rows → index) and is now reused for 'NEW' (40% → should be a full scan). child_number => NULL shows every child cursor of the sql_id; V$SQL_SHARED_CURSOR explains why each child exists.

3d. The plan is gone from the shared pool

SQL

-- Which plans has this SQL had, and when did they change?
SELECT sql_id, plan_hash_value, MIN(snap_id) AS first_snap, MAX(snap_id) AS last_snap,
       ROUND(SUM(elapsed_time_delta) / SUM(executions_delta) / 1e6, 2) AS avg_sec
FROM   dba_hist_sqlstat
WHERE  sql_id = '9k2r7c3x1b4dq' AND executions_delta > 0
GROUP  BY sql_id, plan_hash_value
ORDER  BY first_snap;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('9k2r7c3x1b4dq', 1735962731, NULL, 'TYPICAL'));

Two plan_hash_values with very different avg_sec = the plan flipped. DISPLAY_AWR shows each one. AWR keeps only the top SQL of each snapshot — a light statement may not be there.

3e. Compare two plans (19c+)

SQL

SELECT DBMS_XPLAN.COMPARE_PLANS(
         reference_plan    => cursor_cache_object('9k2r7c3x1b4dq', 0),
         compare_plan_list => plan_object_list(awr_object('9k2r7c3x1b4dq', NULL, NULL, 1735962731)),
         type              => 'TEXT',
         level             => 'TYPICAL')
FROM   dual;

Returns a CLOB: both plans, the differing lines, and the reason (predicate, join order, access path). Verify the exact object constructors on your version with DESC DBMS_XPLAN.

4. How to Read a Plan — the Checklist

Shape first. Children are indented under their parent; a parent consumes its children's output. Among siblings the first executes first. Find the join method (NESTED LOOPS / HASH JOIN / MERGE JOIN) and the access path of each table (FULL / INDEX RANGE SCAN / INDEX UNIQUE SCAN / BY ROWID).
Where does the time go. A-Time is inclusive (parent includes children). Self time of a step = its A-Time minus its children's. Buffers is the more stable measure — seconds change with caching, logical reads do not. Compare Buffers between two versions of a query, not seconds.
E-Rows × Starts vs A-Rows. A gap of 10x or more on any step means the optimizer built the plan on a wrong assumption. Everything above that step is suspect. Causes: stale statistics, missing histogram, correlated columns (use extended statistics), a function on a column, bind peeking.
access vs filter. access = the index was navigated with this predicate. filter = rows were fetched and then discarded. A filter containing TO_NUMBER(, INTERNAL_FUNCTION(, UPPER(, TRUNC( on a column = implicit conversion or function, index unusable — Chapter 3 and Chapter 6.
Starts on the inner side of NESTED LOOPS = number of outer rows. Six or seven digits there = you wanted a hash join.
Pstart / Pstop. Numbers = pruned. KEY = pruned at runtime by binds (fine). ALL on orders = no pruning = Chapter 6.
Memory columns (OMem, 1Mem, Used-Mem, Used-Tmp). Used-Tmp present = the sort or hash spilled to TEMP (one-pass or multi-pass). PGA target too small for this degree, or the operation is bigger than it should be.
Parallel columns (TQ, IN-OUT, PQ Distrib). P->S at the top is normal (results to the coordinator). P->S in the middle = serialization point, the parallel plan is broken there. Chapter 9.
The Note section. "dynamic statistics used", "statistics feedback used for this statement", "SQL plan baseline used", "this is an adaptive plan" — each one tells you the plan is not what plain statistics would give. Read it every time.

5. From PL/SQL — Capturing Plans Automatically

The plan of a statement inside a package is a cursor like any other. Find it by module/action (Chapter 10), and store it after every run: plan changes get caught by a query, not by a phone call.

SQL

CREATE TABLE plan_log (
  captured_at     TIMESTAMP,
  module          VARCHAR2(64),
  sql_id          VARCHAR2(13),
  plan_hash_value NUMBER,
  elapsed_sec     NUMBER,
  plan_text       CLOB);

CREATE OR REPLACE PROCEDURE capture_plans (p_module VARCHAR2) IS
  l_plan CLOB;
BEGIN
  FOR s IN (SELECT sql_id, child_number, plan_hash_value, elapsed_time / 1e6 AS sec
            FROM   v$sql
            WHERE  module = p_module
            ORDER  BY elapsed_time DESC
            FETCH FIRST 5 ROWS ONLY) LOOP
    l_plan := EMPTY_CLOB();
    FOR p IN (SELECT plan_table_output
              FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, 'ALLSTATS LAST'))) LOOP
      l_plan := l_plan || p.plan_table_output || CHR(10);
    END LOOP;
    INSERT INTO plan_log VALUES (SYSTIMESTAMP, p_module, s.sql_id, s.plan_hash_value, s.sec, l_plan);
  END LOOP;
  COMMIT;
END;
/

Call it as the last step of the nightly job (capture_plans('nightly_batch')). Then:

SQL

-- Did any plan change since the previous run?
SELECT sql_id, captured_at, prev_phv, plan_hash_value, elapsed_sec
FROM  (SELECT l.*,
              LAG(plan_hash_value) OVER (PARTITION BY sql_id ORDER BY captured_at) AS prev_phv
       FROM   plan_log l
       WHERE  module = 'nightly_batch')
WHERE  prev_phv IS NOT NULL AND prev_phv <> plan_hash_value;

Inside a definer's-rights procedure, V$SQL needs a direct grant (GRANT SELECT ON v_$sql TO app_user) — a grant through a role is invisible to stored PL/SQL.

6. Privileges

Function	Needs
`DISPLAY`	`PLAN_TABLE` (the public global temporary table exists since 10g — nothing to create)
`DISPLAY_CURSOR`	`SELECT` on `V_$SQL`, `V_$SQL_PLAN`, `V_$SQL_PLAN_STATISTICS_ALL`, `V_$SESSION` — or `SELECT_CATALOG_ROLE`
`DISPLAY_AWR`	`SELECT` on `DBA_HIST_SQL_PLAN`, `DBA_HIST_SQLTEXT`, `V_$DATABASE` + Diagnostics Pack license
`GATHER_PLAN_STATISTICS` hint	nothing — but `statistics_level = ALL` at session/system level costs CPU on every statement

7. Pitfalls

DISPLAY_CURSOR(NULL, ...) shows the wrong statement. With SERVEROUTPUT ON, the last statement is DBMS_OUTPUT.GET_LINES. SET SERVEROUTPUT OFF, or pass the sql_id.
No A-Rows column. The statement ran without GATHER_PLAN_STATISTICS / statistics_level = ALL. Run it again with the hint — the plan without actuals is half the story.
Numbers look absurd. You forgot LAST — A-Rows and Buffers are cumulative over every execution since the cursor was loaded.
EXPLAIN PLAN shows a different plan than production. It always can: no bind peeking, all binds typed VARCHAR2, no adaptive plan, current session settings. Trust DISPLAY_CURSOR.
Several child cursors. Same sql_id, different plans — binds, NLS settings, optimizer parameters. child_number => NULL shows them all; V$SQL_SHARED_CURSOR says why.
"cursor not found". Aged out of the shared pool, or you are in a different container/instance (RAC: GV$SQL). Fall back to DISPLAY_AWR.
A-Time on a full scan is far bigger than its I/O justifies. Look for a PL/SQL function in the filter predicate — its cost hides inside that step. DBMS_HPROF makes it visible.
Fixing the plan with hints. A hint fixes one statement, once, until the data changes. Fix the cause the plan points to: statistics, the predicate, the missing partition key. Hints and SPM baselines are for the cases the optimizer genuinely cannot get right.