Get the Actual Execution Plan with DBMS_XPLAN.DISPLAY_CURSOR

calendar Sep 13, 2026 / · 10 min read · Oracle DBA DBMS_XPLAN DISPLAY_CURSOR Execution Plans GATHER_PLAN_STATISTICS Cursor Cache SQL Tuning Oracle Performance V$SQL_PLAN  ·
Share on: linkedin copy

Get the Actual Execution Plan with DBMS_XPLAN.DISPLAY_CURSOR

Purpose

EXPLAIN PLAN never runs the statement it describes. It asks the optimizer what it would do, based on statistics and cardinality estimates, and prints that guess — which is exactly why a query that looks fine in EXPLAIN PLAN can still run slow in production: skewed data, stale statistics, or a bind variable peeked at parse time can all send the real execution down a different path than the estimate shows. DBMS_XPLAN's DISPLAY_CURSOR table function closes that gap. Instead of estimating, it reads the plan already sitting in the cursor cache for a statement that has actually executed — sourced from V$SQL_PLAN, V$SQL, and, when the statement was parsed with the right hint in place, V$SQL_PLAN_STATISTICS_ALL — and returns the real operations Oracle chose, not the ones it might have chosen.

DBMS_XPLAN runs with the privileges of the calling user rather than the package owner, and DISPLAY_CURSOR specifically requires SELECT or READ privileges on V$SQL_PLAN, V$SESSION, V$SQL_PLAN_STATISTICS_ALL, and V$SQL. Those privileges are granted automatically to any account holding SELECT_CATALOG_ROLE; an account without that role needs each fixed view granted individually before the function will return anything. The function itself takes three optional parameters — a SQL_ID, a CURSOR_CHILD_NO, and a FORMAT string — and if the SQL_ID is omitted, it falls back to the last statement the current session executed, which makes an ad hoc check as simple as running the query, then running the function with no arguments at all.

The package was introduced in Oracle 9i as a replacement for the older utlxpls.sql script and hand-written queries against the plan table, and DISPLAY_CURSOR itself was added later, in Oracle Database 10g Release 1, once the cursor cache became a viable source for after-the-fact plan review rather than only the static PLAN_TABLE that EXPLAIN PLAN populates. That distinction still matters: DBMS_XPLAN.DISPLAY formats whatever was written to a plan table by an EXPLAIN PLAN command, while DISPLAY_CURSOR bypasses the plan table entirely and reads the live cursor cache.

This post covers the DISPLAY_CURSOR syntax, how the GATHER_PLAN_STATISTICS hint combines with the ALLSTATS LAST format to add real row counts and per-step timing to the plan, the additional format predicates that add back columns like cost and bytes or strip out others, and the privilege grants a non-DBA account needs before any of this will run.

Code

 1-- Query 1: plan for the last statement this session executed, no arguments needed
 2SELECT ename FROM emp e, dept d
 3WHERE  e.deptno = d.deptno AND e.empno = 7369;
 4
 5SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
 6
 7-- Query 2: find the SQL_ID and child number for a specific statement first
 8SELECT sql_id, child_number, sql_text
 9FROM   v$sql
10WHERE  sql_text LIKE '%GATHER_PLAN_STATISTICS%';
11
12-- Query 3: display the plan for that specific SQL_ID and child number
13SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gwp663cqh5qbf', 0));
14
15-- Query 4: join v$sql to the table function in one statement instead of two lookups
16SELECT t.*
17FROM   v$sql s, TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t
18WHERE  s.sql_text LIKE '%TOTO%';
19
20-- Query 5: add GATHER_PLAN_STATISTICS to the statement, then pull real row counts and timing
21SELECT /*+ GATHER_PLAN_STATISTICS */ d.dname, COUNT(*)
22FROM   emp e, dept d
23WHERE  e.deptno = d.deptno
24GROUP  BY d.dname;
25
26SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));
27
28-- Query 6: ALLSTATS LAST hides estimated cost and bytes by default -- add them back
29SELECT * FROM TABLE(
30  DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'gwp663cqh5qbf', format => 'ALLSTATS LAST +cost +bytes')
31);
32
33-- Query 7: see the join order directly via the outline's LEADING hint
34SELECT * FROM TABLE(
35  DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'gwp663cqh5qbf', format => 'ALLSTATS LAST +outline')
36);
37
38-- Query 8: strip noisy columns from a wide plan for a cleaner read
39SELECT * FROM TABLE(
40  DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'gwp663cqh5qbf', format => 'ALLSTATS LAST -rows -predicate')
41);
42
43-- Grants a non-SYSDBA account needs before DISPLAY_CURSOR will return rows
44GRANT SELECT ON v_$session TO app_owner;
45GRANT SELECT ON v_$sql TO app_owner;
46GRANT SELECT ON v_$sql_plan TO app_owner;
47GRANT SELECT ON v_$sql_plan_statistics_all TO app_owner;

Code Breakdown

Query 1: the zero-argument default

DISPLAY_CURSOR() with no arguments displays the plan for the last SQL statement this session executed — the SQL_ID parameter defaults to NULL and CURSOR_CHILD_NO defaults to 0. This is the fastest path for an ad hoc check: run the query, then immediately run the function call, with no lookup step in between.

Queries 2–4: targeting a specific cursor

Once more than the current session's last statement is in scope, V$SQL supplies the SQL_ID and CHILD_NUMBER pair that identifies exactly which cached cursor to display — useful when checking a statement another session ran, or re-checking one after it has aged further down the list. Query 4 shows the shortcut version: joining V$SQL directly to the table function in a single SELECT, rather than running the lookup and the display as two separate steps.

Query 5: GATHER_PLAN_STATISTICS and ALLSTATS LAST

By default, DISPLAY_CURSOR shows only the optimizer's estimated rows (E-Rows) — the same numbers EXPLAIN PLAN would have produced. To see the actual rows returned and the actual elapsed time at each step (A-Rows, A-Time), the statement has to be parsed with the GATHER_PLAN_STATISTICS hint in place before it runs, and the display call has to set format => 'ALLSTATS LAST'. Without the hint present at parse time, there is no per-step runtime data captured to display, regardless of what format string is passed afterward.

Query 6: restoring cost and bytes

ALLSTATS LAST does not include the estimated Cost or E-Bytes columns by default — it prioritizes the actual-vs-estimated row and timing comparison instead. Appending +cost and +bytes (each preceded by a plus sign) to the format string adds those columns back into the same output, without needing a second query.

Query 7: reading the join order with +outline

DISPLAY_CURSOR's output is a table, not a tree diagram, so a plan with several joins can be hard to read for order alone. Adding +outline to the format string returns the plan's Outline Data block — the full set of hints that reproduces the statement — and the LEADING line inside it lists the tables in the actual join order the optimizer chose, table aliases and all.

Query 8: trimming columns

The - prefix removes information rather than adding it. -rows -predicate in Query 8 drops the E-Rows column and the Predicate Information section from the output — useful on a wide, many-step plan where only the operation names, actual rows, and timing matter for the check at hand.

Grants for a non-DBA account

DISPLAY_CURSOR reads V$SQL_PLAN_STATISTICS_ALL, V$SQL, and V$SQL_PLAN; an account that does not already hold SELECT_CATALOG_ROLE needs each of those fixed views granted explicitly — through their V_$ synonym form — before the function returns any rows for that account. Without the grants, the call fails outright rather than returning a partial or empty plan.

Key Points

  • DISPLAY_CURSOR reads the cursor cache, not the plan table. EXPLAIN PLAN plus DBMS_XPLAN.DISPLAY shows an estimate for a statement that has not run; DISPLAY_CURSOR shows the plan for a statement that already has.
  • Actual row counts and timing require the hint at parse time. GATHER_PLAN_STATISTICS has to be in the SQL text when the statement is first parsed — it cannot be added retroactively to a cursor already sitting in the cache.
  • ALLSTATS LAST hides Cost and E-Bytes by default. Add +cost and +bytes to the format string to bring them back alongside the actual-rows comparison.
  • The three parameters all default sensibly. No SQL_ID means the session's last statement; no CURSOR_CHILD_NO means child 0; no FORMAT means TYPICAL.
  • +outline is the fastest way to confirm join order without manually tracing indentation through a tabular plan.
  • Parallel execution details only appear when the query actually ran parallel — the TQ, INOUT, and PQ Distrib columns are omitted from serial plans automatically, not hidden behind a format flag.
  • SELECT_CATALOG_ROLE covers every privilege DISPLAY_CURSOR needs. Without it, four separate fixed-view grants are required instead.

Insights and Best Practices

Add the hint before the run, not after

Because GATHER_PLAN_STATISTICS has to be present when a statement is parsed, there's no way to go back and collect actual-rows data for a cursor that already ran without the hint. For a statement under active investigation, adding the hint and re-running it — even if that means one extra execution — is the only way to get ALLSTATS LAST data for it.

Use +outline when the tabular layout gets confusing

A plan with four or five joins reads as a flat, indented table, and tracing which table joined to which by indentation alone gets error-prone past a handful of steps. The LEADING line inside +outline's Outline Data block states the join order explicitly, as a plain list of table aliases, which removes the guesswork.

Trim the output for wide production plans

A plan against a real production schema can run to dozens of steps with long object names. Combining - predicates — dropping Predicate Information, Note, or other sections not relevant to the specific question being asked — keeps a long plan legible in a terminal instead of scrolling past what doesn't matter for the check at hand.

Grant the underlying views deliberately

SELECT_CATALOG_ROLE is the simplest path to giving a non-DBA account everything DISPLAY_CURSOR needs, but it also grants read access to a broad set of dictionary and performance views beyond just the four this function touches. For an account that should only ever run DISPLAY_CURSOR and nothing else DBA-adjacent, the four individual grants on V_$SESSION, V_$SQL, V_$SQL_PLAN, and V_$SQL_PLAN_STATISTICS_ALL are the narrower alternative.

Know when the cursor has already aged out

DISPLAY_CURSOR can only show a plan for a cursor still resident in the cursor cache. A statement that ran hours ago and has since been aged out under memory pressure returns nothing from this function — at that point, the choice is either EXPLAIN PLAN plus DISPLAY for a fresh estimate, or DISPLAY_AWR if the statement's plan was captured in an AWR snapshot while it was still active.

When to Use This

  • A query performs differently in production than it did in a test run, and the actual row counts per step — not just the estimates — are needed to find where the divergence starts.
  • Confirming that a hint, a SQL Profile, or a statistics change actually altered the plan the optimizer chose, rather than trusting that it did.
  • Verifying the real join order the optimizer selected on a multi-table query, without manually tracing indentation.
  • Checking whether a statement ran in parallel and, if so, how work was distributed across the parallel query slaves.
  • Comparing E-Rows against A-Rows at each step to find the exact operation where a cardinality estimate went wrong.

Troubleshooting Common Issues

ALLSTATS LAST shows no A-Rows or A-Time values. The statement almost certainly ran without the GATHER_PLAN_STATISTICS hint present at parse time. Re-run the statement with the hint added to the SQL text, then call DISPLAY_CURSOR again against the new child cursor.

Cost and E-Bytes are missing from an ALLSTATS LAST plan. This is expected — that format omits them by default. Append +cost and +bytes to the format string to include them alongside the actual-vs-estimated comparison.

DISPLAY_CURSOR returns no rows at all, or an insufficient-privileges error. Confirm the connecting account holds SELECT_CATALOG_ROLE, or has been granted SELECT on V_$SESSION, V_$SQL, V_$SQL_PLAN, and V_$SQL_PLAN_STATISTICS_ALL individually.

A known SQL_ID returns nothing. The cursor has likely aged out of the cursor cache since it last ran. Check V$SQL for that SQL_ID first — if it isn't there, the cursor is gone and a fresh EXPLAIN PLAN (or an AWR lookup via DISPLAY_AWR, if the plan was captured there) is the remaining option.

References

Posts in this series