You may have received the below error while you tried to lookup the execution plan using DBMS_XPLAN.DISPLAY_CURSOR procedure.
ops$rperumal@PDB10> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 9babjv8yq8ru3, child number 0
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
8 rows selected.
The error is due to the SQL*Plus environment variable SERVEROUTPUT is turned on. One of the Prerequisites for running the package is to turn off SERVEROUTPUT.
ops$rperumal@PDB10> show serveroutput serveroutput ON SIZE 1000000 FORMAT WORD_WRAPPED ops$rperumal@PDB10> SET serveroutput OFF
You should be able to see the execution plan after turning off the serveroutput -
ops$rperumal@PDB10> SELECT count(*) FROM t;
COUNT(*)
----------
0
ops$rperumal@PDB10> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 5tjqf7sx5dzmj, child number 0
-------------------------------------
SELECT count(*) FROM t
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 16 |
| 2 | TABLE ACCESS FULL| T | 1 | 1 | 0 |00:00:00.01 | 16 |
-------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
17 rows selected.
ops$rperumal@PDB10>
