使用dbms_xplan.display_cursor关于cannot fetch plan for SQL_ID问题的解决方法

1. 错误描述

使用dbms_xplan.display_cursor查看执行计划时，提示

richy@ORCL11G>select ename,dname,loc from scott.emp a,scott.dept b where a.deptno=b.deptno and a.empno=7788;   ENAME      DNAME          LOC ---------- -------------- ------------- SCOTT      RESEARCH       DALLAS   richy@ORCL11G>select * from table(dbms_xplan.display_cursor(null,null)); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- SQL_ID  9babjv8yq8ru3, child number 1   BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 1       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. 错误原因

在asktom.com上有关于此问题的解释

Followup   September 2, 2008 - 2pm UTC: do you see the sql it is showing - dbms_output.get_lines....  the last sql your session executed was in fact that call - sqlplus does that when you have set serveroutput on .

意思是如果设置serveroutput值为on的话，sqlplus会认为你的session执行的最后一个语句为dbms_output.get_lines，因此无法回去执行计划。

3. 解决方法

set serveroutput off

4. 验证处理结果

richy@ORCL11G>set serveroutput off richy@ORCL11G>select ename,dname,loc from scott.emp a,scott.dept b where a.deptno=b.deptno and a.empno=7788;   ENAME      DNAME          LOC ---------- -------------- ------------- SCOTT      RESEARCH       DALLAS   richy@ORCL11G>select * from table(dbms_xplan.display_cursor(null,null));   PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- SQL_ID  59bqwmzf2t3du, child number 0 ------------------------------------- select ename,dname,loc from scott.emp a,scott.dept b where a.deptno=b.deptno and a.empno=7788   Plan hash value: 2385808155   ---------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |       |       |     2 (100)|          | |   1 |  NESTED LOOPS                |         |     1 |    33 |     2   (0)| 00:00:01 | |   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    13 |     1   (0)| 00:00:01 | |*  3 |    INDEX UNIQUE SCAN         | PK_EMP  |     1 |       |     0   (0)|          | |   4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     1   (0)| 00:00:01 | |*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("A"."EMPNO"=7788)    5 - access("A"."DEPTNO"="B"."DEPTNO")   24 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29616091/viewspace-1253470/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29616091/viewspace-1253470/