解决 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>