admin@CADB> select * fromtable(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------

SQL_ID  9babjv8yq8ru3, child number 3

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 3

Please verify value of SQL_ID and CHILD_NUMBER;

It could also be that the plan isno longer incursor cache (check v$sql_plan)

-->查看serveroutput设置值，此时为ON，再次调用dbms_xplan.display_cursor，此时故障同上

scott@CADB> show serveroutput

serveroutput ONSIZE 1000000 FORMAT WORD_WRAPPED

scott@CADB> select /*+ gather_plan_statistics */ * from dept;

DEPTNO DNAME          LOC

---------- -------------- -------------

10 ACCOUNTING     NEW YORK

20 RESEARCH       DALLAS

30 SALES          CHICAGO

40 OPERATIONS     BOSTON

50 DEVELOPE       CHINA

scott@CADB> select * fromtable(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------

SQL_ID  9babjv8yq8ru3, child number 2

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 2

Please verify value of SQL_ID and CHILD_NUMBER;

It could also be that the plan isno longer incursor cache (check v$sql_plan)

scott@CADB> set serveroutput off;

scott@CADB> select /*+ gather_plan_statistics */ * from dept;

DEPTNO DNAME          LOC

---------- -------------- -------------

10 ACCOUNTING     NEW YORK

20 RESEARCH       DALLAS

30 SALES          CHICAGO

40 OPERATIONS     BOSTON

50 DEVELOPE       CHINA

-->此时成功提取执行计划

scott@CADB> select * fromtable(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------

SQL_ID  08u3pfapxj6g5, child number 0

-------------------------------------

select /*+ gather_plan_statistics */ * from dept

Plan hash value: 3383998547

------------------------------------------------------------------------------------

Id    Operation           Name   Starts   E-Rows   A-Rows     A-Time     Buffers

------------------------------------------------------------------------------------

1    TABLE ACCESS FULL  DEPT        1        5        5  00:00:00.01         8

------------------------------------------------------------------------------------

12 rows selected.

-->下面使用explain plan加载执行计划到plan_table，然后使用dbms_xplan.display提取执行计划

-->从下面的实现中可知，serveroutput不影响从plan_table提取执行计划

scott@CADB> show serveroutput

serveroutput ONSIZE 1000000 FORMAT WORD_WRAPPED

scott@CADB> explain plan forselectcount(*) from emp;

Explained.

scott@CADB> select * fromtable(dbms_xplan.display);

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1858788047

-------------------------------------------------------------------------

Id    Operation          Name           Rows    Cost (%CPU)  Time

-------------------------------------------------------------------------

0   SELECT STATEMENT                      1       1   (0)  00:00:01

1    SORT AGGREGATE                       1

2     INDEXFULL SCAN  I_EMP_DEPTNO      14       1   (0)  00:00:01

-------------------------------------------------------------------------

9 rows selected.

-->很多时候DBA在login.sql 或 glogin.sql 时指定了serveroutput为ON，以便在执行PL/SQL时能正常输出，而在获取SQL的执行计划时，恰恰

-->由于该设置而导致无法提取执行计划。不过serveroutput不影响从plan_table提取执行计划。