首页 » Cloud, ORACLE 9i-23ai » 细说“Error: cannot fetch last explain plan from PLAN_TABLE”

细说“Error: cannot fetch last explain plan from PLAN_TABLE”

  • 2020/01/04
  • Cloud, ORACLE 9i-23ai
  • 645 views
  • 细说“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前确认不被业务应用所使用。

 

Related Posts:

打赏

, ,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：