# 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](../course-plan.md) · Appendix entry: [DBMS_XPLAN](../appendix.md#dbms_xplan) · Pairs with: [DBMS_HPROF deep dive](dbms_hprof.md) (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" '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_value`s 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 1. **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`). 2. **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. 3. **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. 4. **`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. 5. **`Starts` on the inner side of NESTED LOOPS** = number of outer rows. Six or seven digits there = you wanted a hash join. 6. **`Pstart` / `Pstop`.** Numbers = pruned. `KEY` = pruned at runtime by binds (fine). `ALL` on `orders` = no pruning = Chapter 6. 7. **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. 8. **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. 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 1. **`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`. 2. **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. 3. **Numbers look absurd.** You forgot `LAST` — A-Rows and Buffers are cumulative over every execution since the cursor was loaded. 4. **`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`. 5. **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. 6. **"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`. 7. **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. 8. **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.