Oracle Database / SQL Performance & Memory
Inspect a cached execution plan without running EXPLAIN PLAN
A hypothetical plan may differ from the one the application executed. Display the correct cached child cursor when investigating a regression.
Environment and inputs: Oracle Database 19c baseline · SQL*Plus-compatible SQL unless RMAN, DGMGRL, ADRCI or a host shell is specified. Check release, patch level, edition and container first.
Start with this diagnostic
Use the stated SQL, RMAN, DGMGRL or host shell and replace sample names with approved targets. DBA and V$ views need authorized catalog access in the correct container. Do not grant broad privileges for an example.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('REPLACE_SQL_ID', 0, 'TYPICAL'));Read the result
Replace the SQL ID and child number with an existing cursor. The plan describes the cached child; it does not include actual row-source statistics unless collected and requested appropriately.
Resolve and verify
Compare join order, predicates and estimated rows with the known working case. Test the smallest corrective change using the same bind types and data distribution.
Operational precautions
The package requires access to relevant dynamic views. Do not confuse estimated cardinalities with measured row counts.