EXPLAIN PLAN shows the plan the optimizer would probably use. DBMS_XPLAN.DISPLAY_CURSOR shows the plan a statement actually used, from the cursor cache, and with run-time statistics it adds the real row counts next to the estimates. Comparing estimated and actual rows is the quickest way to find why a query is slow.
Code for This Guide
The main examples are in the examples/pkg-performance folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
select * from table(dbms_xplan.display_cursor( sql_id => null, -- null: the last statement of this session cursor_child_no => 0, format => 'BASIC +PREDICATE +ROWSTATS LAST')); -- or 'ALLSTATS LAST'
Row statistics need the GATHER_PLAN_STATISTICS hint, or STATISTICS_LEVEL set to ALL. Reading the cursor cache needs SELECT on V$SQL_PLAN and related views, included in SELECT_CATALOG_ROLE.
Estimated and Actual Rows
Example:
set serveroutput off -- (with SERVEROUTPUT ON, SQLcl runs its own block after each statement to read DBMS_OUTPUT, -- and DISPLAY_CURSOR would show the plan of that block) select /*+ gather_plan_statistics */ count(*) as tickets from tickets t join flights f on f.flight_id = t.flight_id where f.status = 'CANCELLED'; -- the plan of the last statement of this session, with actual row counts select plan_table_output from table(dbms_xplan.display_cursor(format => 'BASIC +PREDICATE +ROWSTATS LAST')); set serveroutput on
Output:
TICKETS
__________
8
PLAN_TABLE_OUTPUT
__________________________________________________________________________________
EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */ count(*) as tickets from tickets
t join flights f on f.flight_id = t.flight_id where f.status =
'CANCELLED'
Plan hash value: 1892020674
-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
|* 2 | HASH JOIN | | 1 | 1069 | 8 |
|* 3 | TABLE ACCESS FULL | FLIGHTS | 1 | 952 | 50 |
| 4 | INDEX FAST FULL SCAN| TICKETS_FLIGHT_IX | 1 | 1069 | 1069 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("F"."FLIGHT_ID"="T"."FLIGHT_ID")
3 - filter("F"."STATUS"='CANCELLED')
Note
-----
- this is an adaptive plan
28 rows selected.Starts, E-Rows, and A-Rows show for each step how often it ran, how many rows the optimizer expected, and how many came. The optimizer expected 952 cancelled flights and found 50, so the join was planned for far more rows than it got. The predicate section shows where each condition was applied.
Things to Know
- In SQLcl and SQL*Plus, turn SERVEROUTPUT off first, or the last statement is the tool's own call to read DBMS_OUTPUT.
- For another session's statement, find its SQL_ID in V$SQL and pass it.
- Big differences between E-Rows and A-Rows usually point to missing or stale statistics, or a condition the optimizer cannot estimate.
Related Guides
Conclusion
DBMS_XPLAN.DISPLAY_CURSOR shows the plan a statement really used, and with GATHER_PLAN_STATISTICS the actual rows of each step. Compare E-Rows with A-Rows to find where the optimizer's estimates go wrong.
