注释

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.
---> 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.
---> 临时表 session1:commit，session2:不可查看数据

测试【PLAN_TABLE普通表/基于PLAN_TABLE$基表的同义词】数据保留情况

1）PLAN_TABLE 表...SESSION1 ：

SQL> show user

User is "fescotest_prod"

SQL>

-->查看表类型【是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; ---> 生成执行计划

Explained

SQL> select * from table(dbms_xplan.display()); ---> 查看执行计划

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; ---> 提交事物

Commit complete

---> 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()); ---> 查看执行计划

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>

-->sesion2 可以看到提交的数据 ...可以存放特定SQL的执行计划,,避免去V$SQL_PLAN 查找...利于偶尔优化SQL..看个人想法理解优缺点...

2）基于PLAN_TABLE$ 创建的同义词...SESSION1 ：

SQL> drop table plan_table; ---> 删除表

Table dropped

SQL> SELECT owner,object_name,object_type,object_id FROM DBA_OBJECTS T WHERE T.OBJECT_NAME LIKE 'PLAN_TABLE%'; ---> 查看对象

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$'; ---> 查看表类型

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; ---> 生成执行计划

Explained

SQL> select * from table(dbms_xplan.display()); ---> 查看执行计划

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; ---> 提交

Commit complete

SQL>

---> 基于PLAN_TABLE$ 创建的同义词...SESSION2 ：

SQL> SELECT COUNT(1) FROM plan_table; ---> 查看表数据

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/

祝好~

ITPub博客

Oracle --- PLAN_TABLE$和PLAN_TABLE区别

注释

最新文章