ORACLE学习笔记系列（2）解决 SQLPLUS分析SQL语句出现 'PLAN_TABLE' is old version

解决 SQLPLUS分析SQL语句出现 'PLAN_TABLE' is old version

 

分析sql执行计划时,遇到 'PLAN_TABLE' is old version
 
解决方法: 删除plan_table重建

 

SQL>explain plan for select * from users;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 455 | 19565 | 4 |
| 1 | TABLE ACCESS FULL| USERS | 455 | 19565 | 4 |
-----------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version

11 rows selected.

 

 

--处理如下：

SQL> drop table plan_table purge;

Table dropped.

SQL> @E:\app\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql

Table created.


SQL> clear screen

SQL> SET LINESIZE 10000

SQL> explain plan FOR SELECT * FROM USER_TABLES;

已解释。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
Plan hash value: 4102440123

----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |  2443 |  5604K|   730   (7)| 00:00:09 |
|*  1 |  HASH JOIN                         |               |  2443 |  5604K|   730   (7)| 00:00:09 |
|   2 |   FIXED TABLE FULL                 | X$KSPPCV      |   100 |   196K|     0   (0)| 00:00:01 |
|   3 |   MERGE JOIN CARTESIAN             |               |  2443 |   796K|   729   (7)| 00:00:09 |
|*  4 |    HASH JOIN RIGHT OUTER           |               |  2443 |   665K|   686   (2)| 00:00:09 |
|   5 |     TABLE ACCESS FULL              | SEG$          |  6400 |   281K|    52   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
|*  6 |     HASH JOIN RIGHT OUTER          |               |  2392 |   546K|   633   (1)| 00:00:08 |
|   7 |      TABLE ACCESS FULL             | USER$         |    95 |  1615 |     3   (0)| 00:00:01 |
|*  8 |      HASH JOIN RIGHT OUTER         |               |  2392 |   506K|   630   (1)| 00:00:08 |
|   9 |       TABLE ACCESS FULL            | DEFERRED_STG$ |  2781 | 69525 |     7   (0)| 00:00:01 |
|* 10 |       HASH JOIN OUTER              |               |  2392 |   448K|   622   (1)| 00:00:08 |
|  11 |        NESTED LOOPS OUTER          |               |  2392 |   429K|   563   (1)| 00:00:07 |
|* 12 |         HASH JOIN                  |               |  2392 |   359K|   361   (2)| 00:00:05 |
|  13 |          TABLE ACCESS FULL         | TS$           |     8 |   160 |     5   (0)| 00:00:01 |
|  14 |          NESTED LOOPS              |               |  2392 |   313K|   356   (2)| 00:00:05 |
|* 15 |           TABLE ACCESS FULL        | OBJ$          |  2392 | 86112 |   253   (2)| 00:00:04 |
|* 16 |           TABLE ACCESS CLUSTER     | TAB$          |     1 |    98 |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
|* 17 |            INDEX UNIQUE SCAN       | I_OBJ#        |     1 |       |     0   (0)| 00:00:01 |
|  18 |         TABLE ACCESS BY INDEX ROWID| OBJ$          |     1 |    30 |     2   (0)| 00:00:01 |
|* 19 |          INDEX RANGE SCAN          | I_OBJ1        |     1 |       |     1   (0)| 00:00:01 |
|  20 |        INDEX FAST FULL SCAN        | I_OBJ1        | 74188 |   579K|    58   (0)| 00:00:01 |
|  21 |    BUFFER SORT                     |               |     1 |    55 |   677   (8)| 00:00:09 |
|* 22 |     FIXED TABLE FULL               | X$KSPPI       |     1 |    55 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
   1 - access("KSPPI"."INDX"="KSPPCV"."INDX")
   4 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND
              "T"."TS#"="S"."TS#"(+))
   6 - access("CX"."OWNER#"="CU"."USER#"(+))
   8 - access("T"."OBJ#"="DS"."OBJ#"(+))
  10 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
  12 - access("T"."TS#"="TS"."TS#")
  15 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND BITAND("O"."FLAGS",128)=0)
  16 - filter(BITAND("T"."PROPERTY",1)=0)
  17 - access("O"."OBJ#"="T"."OBJ#")
  19 - access("T"."BOBJ#"="CO"."OBJ#"(+))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
  22 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')

已选择45行。

SQL>