细说“Error: cannot fetch last explain plan from PLAN_TABLE”
前几日有位小兄弟问为什么有时使用explain plan for ….., 然后用dbms_xplan.display查看执行计划， 有时会提示“Error: cannot fetch last explain plan from PLAN_TABLE” 错误？ 其实这个问题在Oracle 12c 以后应该基本不存在，因为这是explain plan一种悄悄的行为变化。
Plan_table
explain plan for 把会SQL的执行计划insert 到一个plan_table的对象中，用dbms_xplan.display可以查看执行计划，也可以手动拼SQL检查， 在老版本中PLAN_TABLE是有utlxplan.sql脚本创建，Oracle 8i后引入的全局临时表后，PLAN_TABLE是有plublic synonym指向sys.PLAN_TABLE$的全局临时表（Global Temporary Table）。
因为当oracle检查同名对象时先是当前schema然后在public synonym，当用户下也有PLAN_TABLE表时,并且使用了set current_schema, explain plan for 生成执行计划，在12c之前版本中使用dbms_xplan.display常常会遇到“Error: cannot fetch last explain plan from PLAN_TABLE”错误。
Demo
通常当我们优化SQL时，对于SQL文本中表名前没有带owner时，为不了修改SQL文本，而是直接alter session set current_schema=xxx的形式来防止相关对象不存在。下面我演示一下这种形为。
— Orace 11g and older
SQL> conn / as sysdba
SQL> explain plan set statement_id='anbob1' for
2 select sysdate from dual;
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 1546270724
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
8 rows selected.
SQL> select statement_id,plan_id,operation,options,object_name,object_alias from sys.plan_table$ where statement_id='anbob1';
STATEMENT_ID PLAN_ID OPERATION OPTIONS OBJECT_NAME OBJECT_ALIAS
-------------------- ---------- ------------------------------ ------------- ------------------------------ -----------------
anbob1 14618 SELECT STATEMENT
anbob1 14618 FAST DUAL DUAL@SEL$1
SQL> alter session set current_schema=ANBOB;
Session altered.
SQL> explain plan set statement_id='anbob2' for
2 select sysdate from dual;
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
Error: cannot fetch last explain plan from PLAN_TABLE
Note:
x2脚本是TanelPoder的脚本包，实际调用的是dbms_xplan.display, 上面显示了set current_schema=anbob后就无法显示了，下面我们查查explain plan把执行计划写进了哪里？
SQL> @o %.plan_table owner object_name object_type status OID D_OID CREATED LAST_DDL_TIME ------------------------- ------------------------------ ------------------ --------- ---------- ---------- ----------------- ----------------- PUBLIC PLAN_TABLE SYNONYM VALID 5127 20141230 17:30:24 20141230 17:30:24 ANBOB PLAN_TABLE TABLE VALID 8944840 8944840 20160104 08:55:43 20160104 08:55:43 SYS PLAN_TABLE$ TABLE VALID 5124 20141230 17:30:24 20141230 17:30:24 14 rows selected. SQL> @syn plan_table OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ PUBLIC PLAN_TABLE SYS PLAN_TABLE$ SQL> select statement_id,plan_id,operation,options,object_name,object_alias from ANBOB.plan_table where statement_id='anbob2'; no rows selected SQL> select statement_id,plan_id,operation,options,object_name,object_alias from sys.plan_table$ where statement_id='anbob2'; STATEMENT_ID PLAN_ID OPERATION OPTIONS OBJECT_NAME OBJECT_ALIAS --------------- -------- ------------------------------ ------------ ---------------- ------------------ anbob2 14619 SELECT STATEMENT anbob2 14619 FAST DUAL DUAL@SEL$1
Note：
执行计划insert 到了connect user ,而不是current_schema user, 所以无法从current_schema中plan_table table中显示执行计划。
解决当然简单了，删除掉不需要的PLAN_TABLE 表。
SQL> drop table anbob.plan_table;
# oracle 12c and beyond
sqlplus / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter session set current_schema=ANBOB;
Session altered.
SQL> explain plan set statement_id='anbob2' for
2 select sysdate from dual;
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
12 rows selected.
SQL> select statement_id,plan_id,operation,options,object_name,object_alias from ANBOB.plan_table where statement_id='anbob2';
STATEMENT_ID PLAN_ID OPERATION OPTIONS OBJECT_NAME OBJECT_ALIAS
------------------------------ ---------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ -----------------------------------------------------------------
anbob2 2286 SELECT STATEMENT
anbob2 2286 FAST DUAL DUAL@SEL$1
SQL> select statement_id,plan_id,operation,options,object_name,object_alias from sys.plan_table$ where statement_id='anbob2';
no rows selected
SQL> drop table ANBOB.plan_table;
Table dropped.
SQL> explain plan set statement_id='anbob3' for
2 select sysdate from dual;
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
8 rows selected.
SQL> select statement_id,plan_id,operation,options,object_name,object_alias from sys.plan_table$ where statement_id='anbob3';
STATEMENT_ID PLAN_ID OPERATION OPTIONS OBJECT_NAME OBJECT_ALIAS
------------------------------ ---------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
anbob3 2287 SELECT STATEMENT
anbob3 2287 FAST DUAL
Note:
在12c, 18c , 19c版本中如果CURRENT_SCHEMA中有同名的PLAN_TABLE，explain plan for 会写入到current_schema而不是connect user(与11g及之前的版本不同)，所以dbms_xplan.display一样也可以显示执行计划，只是会显示”‘PLAN_TABLE’ is old version”字样，同样在新版本中默认也不会再新用户下创建PLAN_TABLE, 只有原来的版本升级上来的情况存在，但依旧不会影响显示执行计划，所以可以安装放心删除用户schema中PLAN_TABLE，但是注意在删除PLAN_TABLE前确认不被业务应用所使用。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。