Show the actual execution plan with row source statistics (ALLSTATS LAST)

Command

-- 1) Collect row source statistics (for this query only):
SET SERVEROUTPUT OFF
SELECT /*+ gather_plan_statistics */ customer_id, SUM(amount)
FROM   customer_app.sales
WHERE  created_at >= DATE '2026-06-01'
GROUP  BY customer_id;
-- or for the whole session: ALTER SESSION SET statistics_level = ALL;

-- 2) Plan of the last cursor run in the same session (E-Rows vs A-Rows):
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
         sql_id          => NULL,
         cursor_child_no => NULL,
         format          => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'));

-- 3) A cursor from another session / from earlier:
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>', NULL, 'ALLSTATS LAST +PEEKED_BINDS'));

What it does

EXPLAIN PLAN is an estimate; this shows the plan that actually ran, with Starts, E-Rows (estimate), A-Rows (actual), A-Time, Buffers and memory usage per step. It is the most direct way to find the step where the cardinality misestimate starts.

When to use it

When tuning a single slow SQL, to prove the need for statistics/histograms, and to measure the effect of a hint or index change.

Caution

No license required. With SET SERVEROUTPUT ON, sql_id=>NULL shows the DBMS_OUTPUT call as the last cursor; set it OFF first. Do not enable statistics_level=ALL system-wide; it adds noticeable CPU overhead. The hint changes the SQL text, so it gets a new sql_id. The user needs read access to V$SQL_PLAN_STATISTICS_ALL (SELECT_CATALOG_ROLE).