Recently, an error "error: cannot fetch last explain plan from plan" was encountered. Therefore, the following scenarios were studied for details:

 

 

1: When you forget to use EXPLAIN PLAN in front of the SQL statement, and then use select * from table (DBMS? Xplan. Display) to view the execution plan of the specific SQL, you will encounter the error "error: cannot fetch last EXPLAIN PLAN from plan? Table". As follows:

 

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

 

 

In fact, this situation is because the SQL statement forgot to use EXPLAIN PLAN. Generally speaking, EXPLAIN PLAN will put the execution plan corresponding to SQL into plan table. The official documents are as follows:

 

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.

 

If you do not use EXPLAIN PLAN, the execution plan of the corresponding SQL is not put into plan table. If you use EXPLAIN PLAN, ORACLE will fill the plan table with formatted data, so as to present it to the user in a readable format. Personal use of 10046 tracking comparison (comparison of the use of EXPLAIN PLAN and not use of EXPLAIN PLAN), when using EXPLAIN PLAN, the database will insert data into the plan table. As follows:

 

 

2: The plan table table exists under the corresponding user (this may be a complex situation), which may be encountered when using alter session set current menu SCHEMA to set the SCHEMA of the current session.

 

Create a plan? Table under the SCOTT user (the structure is the same, if the structure is different, another error will be reported)

 

 

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); #There will be no error under SCOTT user.

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> 

 

However, after using alter session set current menu SCHEMA to set the SCHEMA of the current session, then press the previous SQL test, you will encounter this error, as follows:

 

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

 

 

In this case, you can use the above script to see if there is a plan table with the same name. In this case, you can rename or delete the plan table table under SCOTT. Of course, you can also use the following methods

 

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> 

 

Of course, it can be further explored, but there is not much value, and in the process of testing, individuals find that there are many other conditions, for example, after solving this error, and then testing, they will find that no error is reported. But the execution plan displayed is still the original SQL (not the execution plan of the current SQL). Of course, there are still some scenarios that may encounter this error. Only two scenarios are described here.