Error: cannot fetch last explain plan from PLAN_TABLE

/********构建测试表并插入记录***************/
SQL> select a from t_sys;

         A
----------
         1
/*****可以显示上述sql的执行计划**********/
SQL> select * from table(dbms_xplan.display_cursor);--显示cursor cache中的执行计划，即shared pool

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------

SQL_ID  gg5wbsdrssnvm, child number 0
-------------------------------------
select a from t_sys

Plan hash value: 789675691

------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     3 (100)|
|   1 |  TABLE ACCESS FULL| T_SYS |     1 |    13 |     3   (0)| 00:00:0

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------

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

Note
-----
   - dynamic sampling used for this statement (level=2)

17 rows selected.

/******报错*************/
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------

Error: cannot fetch last explain plan from PLAN_TABLE

/****查阅官方手册display函数显示plan_table中的执行计划,当然你也可以指定你的执行计划表,但你的执行计划表与要plan_table结构相同*****/
DISPLAY Function
 
This table function displays the contents of the plan table.
 
In addition, you can use this table function to display any plan (with or without statistics) stored
in a table as long as the columns of this table are named the same as columns of the plan table
(or V$SQL_PLAN_STATISTICS_ALL if statistics are included). You can apply a predicate on the
specified table to select rows of the plan to display.

SQL> explain plan for select * from t_oa;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3582308059
--------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     2 |    26 |     2   (0)| 00:00:01
|   1 |  PX COORDINATOR      |          |       |       |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |     2 |    26 |     2   (0)| 00:00:01
|   3 |    PX BLOCK ITERATOR |          |     2 |    26 |     2   (0)| 00:00:01
|   4 |     TABLE ACCESS FULL| T_OA     |     2 |    26 |     2   (0)| 00:00:01
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
 
15 rows selected
 
SQL> select count(*) from plan_table;
 
  COUNT(*)
----------
         5
 
SQL>

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

转载于:http://blog.itpub.net/9240380/viewspace-757211/