Error: cannot fetch last explain plan from PLAN_TABLE
最近遇到了错误“error: cannot fetch last explain plan from plan_table”，于是稍微研究了一下哪些场景下碰到这种错误，具体参考下面案例：
1：忘记使用explain plan放在sql语句前面，然后使用使用select * from table(dbms_xplan.display)查看具体sql的执行计划时，就会遇到错误“error: cannot fetch last explain plan from plan_table”。如下所示：
sql> show user;
user is "sys"
sql> select *
2 from scott.emp
3 where hiredate between '01-jan-1981' and '01-apr-1981';
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 allen salesman 7698 20-feb-81 1600 300 30
7521 ward salesman 7698 22-feb-81 1250 500 30
sql> col plan_table_output for a180;
sql> select * from table(dbms_xplan.display);
plan_table_output
--------------------------------------------------------------------------
error: cannot fetch last explain plan from plan_table
其实，这种情形是因为sql语句中忘记使用explain plan，一般而言explain plan会将sql对应的执行计划放入plan_table。官方文档介绍如下：
the explain plan statement displays execution plans chosen by the oracle optimizer for select, update, insert, and delete statements. a statement's execution plan is the sequence of operations oracle performs to run the statement. the row source tree is the core of the execution plan.
如果没有使用explain plan，那么没有将对应sql的执行计划放进plan_table，而如果使用explain plan，那么oracle会用格式化的数据填充plan_table表，以便以易读的格式呈现给用户。个人使用10046跟踪对比了一下（对比使用explain plan和不使用explain plan两种情况），使用explain plan时，数据库会向plan_table插入数据。如下所示：
2：对应的用户下存在plan_table表（这个可能情况比较复杂），然后使用alter session set current_schema设置当前会话的schema时可能会遇到这种场景。
在scott用户下创建一个plan_table（结构一样，如果结构不一样，会报另外一种错误）
sql> show user;
user is "scott"
sql>create table plan_table as
select statement_id,
plan_id,
timestamp,
remarks,
operation,
options,
object_node,
object_owner,
object_name,
object_alias,
object_instance,
object_type,
optimizer,
search_columns,
id,
parent_id,
depth,
position,
cost,
cardinality,
bytes,
other_tag,
partition_start,
partition_stop,
partition_id,
to_lob(other) as other,
other_xml as other_xml,
distribution,
cpu_cost,
io_cost,
temp_space,
access_predicates,
filter_predicates,
projection,
time,
qblock_name
from plan_table;
sql> explain plan for
2 select * from dual;
explained.
sql> select * from table(dbms_xplan.display); #scott用户下不会出错。
plan_table_output
----------------------------------------------------------------------------
plan hash value: 272002086
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | table access full| dual | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
sql>
但是我们使用alter session set current_schema设置当前会话的schema后，那么再按之前的sql测试，就会遇到这个错误，如下所示：
sql> show user;
user is "sys"
sql> alter session set current_schema=scott;
session altered.
sql> explain plan for
2 select *
3 from scott.emp
where hiredate between '01-jan-1981' and '01-apr-1981'; 4
explained.
sql> col plan_table_output for a180;
sql> select * from table(dbms_xplan.display);
plan_table_output
--------------------------------------------------------------------------------
error: cannot fetch last explain plan from plan_table
sql> set linesize 1200
sql> select owner,object_name,object_type,created from all_objects
2 where object_name like 'plan_table%'
3 and owner in (sys_context('userenv','current_schema'),'public','sys');
owner object_name object_type created
------------------------------ ------------------------------ ------------------- ---------
sys plan_table$ table 24-may-15
public plan_table synonym 30-jun-05
scott plan_table table 21-dec-19
如果遇到这种情况，可以使用上面脚本看看是否存在同名的plan_table，这种情况下，可以将scott下的plan_table表重命名或删除即可。当然也可以用下面方法
sql> explain plan into scott.plan_table for
2 select *
3 from scott.emp
where hiredate between '01-jan-1981' and '01-apr-1981';
explained.
sql> col plan_table_output for a180;
sql> select * from table(dbms_xplan.display);
plan_table_output
---------------------------------------------------------------------------------------
plan hash value: 3956160932
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | 2 | 74 | 2 (0)| 00:00:01 |
|* 1 | table access full| emp | 2 | 74 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
plan_table_output
------------------------------------------------------------------------------------------------------------
1 - filter("hiredate"<=to_date(' 1981-04-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') and "hiredate">=to_date(' 1981-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
15 rows selected.
sql>
当然，还可以更深入的探究，只是没有太多价值，而且个人在测试过程中，发现还有许多其它状况，例如解决了这个错误后，再去测试，就发现不报错。但是显示的执行计划还是原来sql（不是当前sql的执行计划）......... 。当然也不排除还有一些场景可能遇到这个错误。这里仅仅描述了两种场景。