You may have received the below error while you tried to lookup the execution plan using DBMS_XPLAN.DISPLAY_CURSOR procedure.
1 2 3 4 5 6 7 8 9 10 11 12 13 | select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------SQL_ID 9babjv8yq8ru3, child number 0BEGIN 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.
1 2 3 | show serveroutputserveroutput ON SIZE 1000000 FORMAT WORD_WRAPPEDops$rperumal@PDB10> SET serveroutput OFF |
You should be able to see the execution plan after turning off the serveroutput –
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | SELECT count(*) FROM t; COUNT(*)---------- 0ops$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 tPlan 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 statement17 rows selected. |
It didnt worked for me
SQL> set serveroutput offSQL> select plan_table_output from table(dbms_xplan.display_cursor(FORMAT=>'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID 5qgz1p0cut7mx, child number 0BEGIN DBMS_OUTPUT.DISABLE; END;NOTE: cannot fetch plan for SQL_ID: 5qgz1p0cut7mx, CHILD_NUMBER: 0Please 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)You will not be able to obtain the execution plan of your last succesfully executed statement, if your—first—attempt obtain the execution plan fails (line # 2). You either need to reexecute your original SQL prior to executing line # 2 or obtain the execution plan by supplying the SQL_ID and CHILD_NUMBER. Refer my post that shows basic methods to obtain the execuction plan.
Yes that worked. Thanks
Thank you. That worked.