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”。

show user;
USER is "SYS"

SELECT * FROM SCOTT.EMP 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

COL PLAN_TABLE_OUTPUT FOR A180;
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插入数据。如下所示：
Pasted image 20230411091115.png

2：对应的用户下存在PLAN_TABLE表（这个可能情况比较复杂），然后使用ALTER SESSION SET CURRENT_SCHEMA设置当前会话的SCHEMA时可能会遇到这种场景。
在SCOTT用户下创建一个PLAN_TABLE（结构一样，如果结构不一样，会报另外一种错误）

SHOW USER;
USER is "SCOTT"

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;

EXPLAIN PLAN FOR SELECT * FROM DUAL;

#SCOTT用户下不会出错
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

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 |
--------------------------------------------------------------------------
但是我们使用ALTER SESSION SET CURRENT_SCHEMA设置当前会话的SCHEMA后，那么再按之前的SQL测试，就会遇到这个错误，如下所示：

show user;
USER is "SYS"

alter session set current_schema=scott;

EXPLAIN PLAN FOR SELECT count(*) FROM scott.emp;

19c 未出现错误
COL PLAN_TABLE_OUTPUT FOR A180;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: cannot fetch last explain plan from PLAN_TABLE


SET LINESIZE 122
col OWNER for a10
col OBJECT_NAME for a20
col OBJECT_TYPE for a20
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED
FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE 'PLAN_TABLE%'
AND OWNER IN (SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'), 'PUBLIC', 'SYS');

OWNER OBJECT_NAME OBJECT_TYPE CREATED
---------- -------------------- -------------------- -------------------
SYS PLAN_TABLE$ TABLE 2021-09-15 11:54:44
SYS PLAN_TABLE_OBJECT TYPE 2021-09-15 11:54:45
SYS PLAN_TABLE_OBJECT TYPE BODY 2021-09-15 12:04:13
PUBLIC PLAN_TABLE SYNONYM 2021-09-15 11:54:44
PUBLIC PLAN_TABLE_OBJECT SYNONYM 2021-09-15 11:54:45
SCOTT PLAN_TABLE TABLE 2023-04-11 09:26:06
如果遇到这种情况，可以使用上面脚本看看是否存在同名的PLAN_TABLE，这种情况下，可以将SCOTT下的PLAN_TABLE表重命名或删除即可。当然也可以用下面方法

EXPLAIN PLAN INTO SCOTT.PLAN_TABLE FOR xxx

EXPLAIN PLAN INTO SCOTT.PLAN_TABLE FOR SELECT * FROM SCOTT.EMP WHERE HIREDATE BETWEEN '01-JAN-1981' AND '01-APR-1981';

COL PLAN_TABLE_OUTPUT FOR A180;
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'))