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.csdn.net/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函数的使用
