注释
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
-----------------------------------------------------------------
| 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> 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
-----------------------------------------------------------------
| 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 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
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 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博客 ” ,链接:http://blog.itpub.net/28602568/viewspace-1260334/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28602568/viewspace-1260334/