使用dbms_xplan.display_cursor关于cannot fetch plan for SQL_ID问题的解决方法
- 错误描述
- 错误原因
- 解决方法
- 验证处理结果
使用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) |
在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，因此无法回去执行计划。
set serveroutput off
|
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博客 ” ，链接：https://blog.itpub.net/29616091/viewspace-1253470/ ，如需转载，请注明出处，否则将追究法律责任。
