cannot fetch plan for SQL_ID

SQL tuning过程中离不开分析SQL语句的执行计划。在一次提取执行计划的时候碰到cannot fetch plan for SQL_ID的错误提示。根据错误
提示来看需要检查SQL的子游标或该执行计划不在v$sql_plan表中，而这种情况一般不存在。因为刚刚执行过的SQL语句不可能这么快从v$sql_plan
移除。下面给出错误描述及处理办法。

1、故障现象 -->使用display_cursor提取执行计划失败 admin@CADB> select * from table(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 is no longer in cursor cache (check v$sql_plan) -->查看serveroutput设置值，此时为ON，再次调用dbms_xplan.display_cursor，此时故障同上 scott@CADB> show serveroutput serveroutput ON SIZE 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 * from table(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 is no longer in cursor cache (check v$sql_plan) 2、解决故障 -->关闭serveroutput选项 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 /**************************************************/ /* Author: Robinson Cheng */ /* Blog: http://blog.youkuaiyun.com/robinson_0612 */ /* MSN: robinson_0612@hotmail.com */ /* QQ: 645746311 */ /**************************************************/ -->此时成功提取执行计划 scott@CADB> select * from table(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 ON SIZE 1000000 FORMAT WORD_WRAPPED scott@CADB> explain plan for select count(*) from emp; Explained. scott@CADB> select * from table(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 | INDEX FULL 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提取执行计划。

和执行计划相关链接：

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

启用 AUTOTRACE 功能