代码改变世界

Error: cannot fetch last explain plan from PLAN_TABLE

2019-12-23 15:08  潇湘隐者  阅读(...)  评论(...编辑  收藏

最近遇到了错误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

 

clip_image001

 

其实,这种情形是因为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插入数据。如下所示:

 

clip_image002

 

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的执行计划)......... 。当然也不排除还有一些场景可能遇到这个错误。这里仅仅描述了两种场景。