註釋

PLAN_TABLE is the default table for results of the EXPLAIN PLAN statement. It is created by utlxplan.sql, and it contains one row for each step in the execution plan.
---&gt session1:commit，session2:可檢視資料

PLAN_TABLE$ is a global temporary table accessible from any schema .It is created by catplan.sql ，It also creates the plan_id sequence number.
---&gt 臨時表 session1:commit，session2:不可檢視資料

測試【PLAN_TABLE普通表/基於PLAN_TABLE$基表的同義詞】資料保留情況

1）PLAN_TABLE 表...SESSION1 ：

SQL> show user

User is "fescotest_prod"

SQL>

--&gt檢視錶型別【是table 還是synonym】

SQL> SELECT t.OBJECT_NAME,t.OBJECT_TYPE FROM User_Objects T WHERE T.OBJECT_NAME='PLAN_TABLE';

OBJECT_NAME OBJECT_TYPE

-------------------------------------------------------------------------------- -------------------

PLAN_TABLE TABLE

SQL> explain plan for select 1 from dual; ---&gt 生成執行計劃

Explained

SQL> select * from table(dbms_xplan.display()); ---&gt 檢視執行計劃

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1388734953

-----------------------------------------------------------------

-----------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------

8 rows selected

SQL> commit; ---&gt 提交事物

Commit complete

---&gt PLAN_TABLE 表...SESSION2 ：

檢視資料：

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Connected as sinotest@192.168.0.59/testdb

SQL> show user

User is "fescotest_prod"

SQL> select * from table(dbms_xplan.display()); ---&gt 檢視執行計劃

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1388734953

-----------------------------------------------------------------

-----------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------

8 rows selected

SQL> select count(1) from plan_table;

COUNT(1)

----------

2

SQL>

--&gtsesion2 可以看到提交的資料 ...可以存放特定SQL的執行計劃,,避免去V$SQL_PLAN 查詢...利於偶爾最佳化SQL..看個人想法理解優缺點...

2）基於PLAN_TABLE$ 建立的同義詞...SESSION1 ：

SQL> drop table plan_table; ---&gt 刪除表

Table dropped

SQL> SELECT owner,object_name,object_type,object_id FROM DBA_OBJECTS T WHERE T.OBJECT_NAME LIKE 'PLAN_TABLE%'; ---&gt 檢視物件

OWNER                          OBJECT_NAME OBJECT_TYPE          OBJECT_ID
------------------------------ ------------------------------ ------------------- ----------
SYS                            PLAN_TABLE$                    TABLE                    59898
PUBLIC                         PLAN_TABLE                     SYNONYM 8709

SQL>

SQL> SELECT T.OWNER,T.TABLE_NAME,T.TEMPORARY FROM DBA_TABLES T WHERE T.TABLE_NAME='PLAN_TABLE$'; ---&gt 檢視錶型別

OWNER TABLE_NAME TEMPORARY

------------------------------ ------------------------------ ---------

SYS PLAN_TABLE$ Y --臨時表(session/事物級別）該表是[on commit preserve rows]session級，而不是[on commit delete rows]事物級

SQL>

SQL> explain plan for select 1 from dual join (select 1 from dual ) on 1=1; ---&gt 生成執行計劃

Explained

SQL> select * from table(dbms_xplan.display()); ---&gt 檢視執行計劃

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3033775561

-----------------------------------------------------------------

-----------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 4 (0)| 00:00:01 |

| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

| 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------

10 rows selected

SQL>

SQL> commit; ---&gt 提交

Commit complete

SQL>

---&gt 基於PLAN_TABLE$ 建立的同義詞...SESSION2 ：

SQL> SELECT COUNT(1) FROM plan_table; ---&gt 檢視錶資料

COUNT(1)

----------

0

SQL>

總結：

【plan_table、基於PLAN_TABLE$基表建立同義詞】都可以理解是檢視執行計劃用的表，如庫中沒有該物件(或沒該物件的許可權),那估算的執行計劃都是不可以用的；包括【explain plan for /SET AUTOT[RACE]...等】

PLAN_TABLE普通表提交其他會話可以看資料【session1:commit，session2可查看資料】、同義詞PLAN_TABLE session級臨時表【session1:commit，session2不可檢視資料】

若想再瞭解下請檢視部落格 http://blog.itpub.net/28602568/viewspace-1097288/

祝好~

Oracle --- PLAN_TABLE$和PLAN_TABLE區別

註釋

相關文章