EXPLAIN PLAN vs Runtime Plans, DBMS_XPLAN, Predicate Information, and Row-Source Metrics

Learning outcomes

A developer pastes an EXPLAIN PLAN into an incident and says it proves the slow request used a hash join that returned 10 rows. It proves neither claim. EXPLAIN PLAN asks the optimizer to estimate a plan; the application may have executed another child cursor with different binds, statistics, optimizer environment, or adaptive choices. This lesson moves from predicted shape to executed row-source evidence.

1. EXPLAIN PLAN answers “what would the optimizer choose?”

EXPLAIN PLAN FOR writes estimated plan rows to a plan table. It does not execute the query, has no actual row counts, and need not match a child cursor already used by an application. It remains useful for design exploration and as a fallback when cursor privileges are unavailable.

EXPLAIN PLAN SET STATEMENT_ID='SH09L3_EXPLAIN' FORSELECT c.case_id,c.amount FROM servicehub_plan_case cWHERE c.status_code='ESCALATED' AND c.region_code='BAKU';SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,'SH09L3_EXPLAIN','TYPICAL +ROWS +PREDICATE'));

2. DISPLAY_CURSOR shows the loaded child cursor

DBMS_XPLAN.DISPLAY_CURSOR formats a plan from the cursor cache. Runtime I/O/row-source statistics are available when collected, for example with /*+ GATHER_PLAN_STATISTICS */ or STATISTICS_LEVEL=ALL. The hint is preferable for a focused diagnostic because setting ALL broadly can add overhead.

SELECT /*+ GATHER_PLAN_STATISTICS */ case_id,amountFROM servicehub_plan_caseWHERE status_code='ESCALATED' AND region_code='BAKU'ORDER BY case_id;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +PREDICATE +NOTE'));

E-Rows are optimizer estimates. A-Rows are rows produced by the operation for the measured execution. Starts counts operation starts. Buffers measures logical buffer activity, not just physical reads. MEMSTATS can expose workarea memory/spill behavior for suitable operators when statistics are available.

3. Use exact fixed-view privileges

Oracle documents that DISPLAY_CURSOR requires read/select access to V$SQL, V$SQL_PLAN, V$SQL_PLAN_STATISTICS_ALL, and V$SESSION. For the disposable course PDB, a DBA can grant only those underlying fixed views rather than a broad catalog role.

GRANT SELECT ON V_$SQL TO servicehub_owner;GRANT SELECT ON V_$SQL_PLAN TO servicehub_owner;GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO servicehub_owner;GRANT SELECT ON V_$SESSION TO servicehub_owner;

REVOKE SELECT ON V_$SQL FROM servicehub_owner;REVOKE SELECT ON V_$SQL_PLAN FROM servicehub_owner;REVOKE SELECT ON V_$SQL_PLAN_STATISTICS_ALL FROM servicehub_owner;REVOKE SELECT ON V_$SESSION FROM servicehub_owner;

If these privileges are not available, keep the conceptual lab with DBMS_XPLAN.DISPLAY and have a DBA capture the executed cursor. Do not jump to AWR: on EE/EE-ES AWR/ASH require Diagnostics Pack.

4. Find the first material estimate error

If a filter estimates 10 rows and actually produces 8,000, every downstream join/sort is costed with a distorted input. Diagnose from the leaves upward and identify where E-Rows first diverge materially from A-Rows. Common causes include skew, correlation, stale statistics, expressions without representative statistics, bind-value sensitivity, and data changes.

5. Wrong evidence: EXPLAIN PLAN plus stopwatch

Elapsed time from one manual execution is affected by cache, client fetching, load, and network. EXPLAIN PLAN is estimated. The repair is to execute the exact SQL with representative binds, capture the matching child cursor with scoped statistics, and compare row-source metrics.

6. Hands-on lab: manufacture and repair an estimate problem

DROP TABLE servicehub_plan_case IF EXISTS PURGE;CREATE TABLE servicehub_plan_case(case_id NUMBER PRIMARY KEY,status_code VARCHAR2(12) NOT NULL,region_code VARCHAR2(12) NOT NULL,amount NUMBER(10,2) NOT NULL);INSERT INTO servicehub_plan_caseSELECT LEVEL,CASE WHEN LEVEL<=9900 THEN 'CLOSED' ELSE 'ESCALATED' END,CASE WHEN MOD(LEVEL,10)=0 THEN 'BAKU' ELSE 'NORTH' END,MOD(LEVEL*47,10000)/10FROM dual CONNECT BY LEVEL<=10000;CREATE INDEX sh09_l3_status_region_ix ON servicehub_plan_case(status_code,region_code);BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER,'SERVICEHUB_PLAN_CASE',cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1'); END;/

SELECT /*+ GATHER_PLAN_STATISTICS */ case_id,amountFROM servicehub_plan_case WHERE status_code='ESCALATED' AND region_code='BAKU';SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +PREDICATE +NOTE'));BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER,'SERVICEHUB_PLAN_CASE',cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE AUTO'); END;/SELECT /*+ GATHER_PLAN_STATISTICS */ case_id,amountFROM servicehub_plan_case WHERE status_code='ESCALATED' AND region_code='BAKU';SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +PREDICATE +NOTE'));DROP TABLE servicehub_plan_case PURGE;

Do not require a specific plan change. Compare the estimates, actual rows, buffers, and predicates; the optimizer is allowed to keep the same plan if it remains cheapest.

7. Production judgment

Use EXPLAIN PLAN for estimated design work and DISPLAY_CURSOR for actual loaded-cursor evidence. Capture the right SQL ID/child where multiple children exist. Scoped row-source instrumentation is usually more responsible than enabling global ALL statistics just to inspect one SQL. The mandatory path does not require AWR, ASH, SQL Monitor, Diagnostics Pack, or Tuning Pack.

Lesson 4 uses these metrics to explain why nested loops, hash joins, sort merge joins, bloom filters, parallel operations, and adaptive branches fit different row-flow shapes.

Authoritative references

• DBMS_XPLAN — DISPLAY_CURSOR, ALLSTATS/LAST and privileges
• V$SQL_PLAN_STATISTICS_ALL — row-source metrics
• Query Optimizer Concepts — estimate context
• Licensing Information — AWR/ASH/SQL Monitor pack boundaries