In my day-to-day
performance tuning activities, I use DBMS_XPLAN.DISPLAY_CURSOR quite often to
display the execution plan of the last SQL executed in SQL*Plus, but it
recently failed with the following message:
JMEHTA@ORA11203>
select * from table(dbms_xplan.display_cursor) ;
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)
DBMS_XPLAN.DISPLAU_CURSOR
was showing the text of a SQL statement that I didn't execute. The fix to this
issue is to disable SERVEROUTPUT via SET SERVEROUTPUT OFF so that SQL*Plus
won’t execute the statement to fetch the output of DBMS_OUTPUT.PUT_LINE, if
any.
JMEHTA@ORA11203>
set serverout off
JMEHTA@ORA11203>
select * from table(dbms_xplan.display_cursor) ;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID gc5ac0ywrn17r, child number 0
-------------------------------------
SELECT /*+
FULL(TP) */ * FROM TP WHERE ID = 1
Plan hash
value: 1244819442
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 (100)| |
| 1 |
TABLE ACCESS BY INDEX ROWID| TP
| 126 | 8190 |
1 (0)| 00:00:01 |
|* 2 |
INDEX RANGE SCAN |
TP_ID_IDX | 50 | |
1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
It works!
A simple fix, but it
took me few minutes to figure it out. A good idea, but not for
DBMS_XPLAN.DISPLAY_CURSOR. Hopefully, if you encounter this issue, you will
have to spend less time figuring it out.
No comments:
Post a Comment