注释
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[$]表
---> 1)删除全部包含plan_table所有对象...
---> 2)查看执行计划,看拨错情况...对报错进行10024事件跟踪 进行分析
---> 3)解决 报错 ..
---> 4-1)若sys用户存在plan_table，plan_table$基表的公有同义词 也存在会优先选择哪个表呢？
---> 4-2)若普通用户存在plan_table，plan_table$基表的公有同义词 也存在会优先选择哪个表呢？
---> 5)2个PLAN_TABLE【$】表创建路径/脚本详情
---> 总结(2个表区别)
---> 1)查找包含plan_table对象的SQL：
SELECT (CASE
WHEN OBJECT_TYPE = 'TABLE' THEN
'DROP TABLE ' || OWNER || '.' || OBJECT_NAME || ' PURGE;'
WHEN OBJECT_TYPE = 'SYNONYM' AND OWNER = 'PUBLIC' THEN
'DROP PUBLIC SYNONYM ' || OBJECT_NAME || ' PURGE;'
WHEN OBJECT_TYPE = 'SYNONYM' AND OWNER != 'PUBLIC' THEN
'DROP SYNONYM ' || OBJECT_NAME || ' PURGE;'
END)
FROM DBA_OBJECTS T
WHERE T.OBJECT_NAME LIKE 'PLAN_TABLE%' ; --> 查询包含plan_table对象的SQL
SQL > drop table SYS.PLAN_TABLE$; --> 删除基表
Table dropped
SQL > drop table plan_table;--> 删除普通表
Table dropped
SQL > drop PUBLIC synonym PLAN_TABLE;-->(若不删除同义词 explain for 会报错ORA-00980: 同义词转换不再有效 )
Synonym dropped
SQL >
总结：
严格要求删除全部包含plan_table所有对象...是因为生成执行计划时，后台会向plan_table对象[可能是普通表/同义词..等]insert数据，若存在，将不会报错...体验不出plan_table的效果~
---> 2)查看执行计划,看拨错情况...对报错进行10046事件跟踪 进行分析
1)plan_table 对象全部删除后, 查看select 1 from dual 的执行计划：
SQL> explain plan for select 1 from dual;
explain plan for select 1 from dual
*
第 1 行出现错误:
ORA-02402: 未找到 PLAN_TABLE --- >其实已经说明白了...没plan_table对象产生执行计划...
2)10046事件跟踪下报错信息
① 确认10046事件跟踪位置
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest' ; --- > 查看trace文件路径
VALUE
------------------------------------------------------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\UDUMP
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
E:\oracle\product\10.2.0\db_1\BIN>del E:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\UDUMP\* --- >删除下所有文件，方便查找每次生产的文件
E:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\UDUMP\*, 是否确认(Y/N)? y
E:\oracle\product\10.2.0\db_1\BIN>dir E:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\UDUMP --- >查看文件夹对象信息...
.......
2014/08/29 14:55
2014/08/29 14:55
0 个文件 0 字节
2 个目录 45,554,696,192 可用字节
② 开始10046事件跟踪
E:\oracle\product\10.2.0\db_1\BIN>sqlplus.exe / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 8月 29 14:56:06 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter session set events '10046 trace name context forever, level 12'; --- > 开启10046 事件跟踪
会话已更改。
已用时间: 00: 00: 00.10
SQL> explain plan for select 1 from dual; --- > 操作
explain plan for select 1 from dual
*
第 1 行出现错误:
ORA-02402: 未找到 PLAN_TABLE
SQL> alter session set events '10046 trace name context off'; --- > 关闭10046 事件跟踪
会话已更改。
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
E:\oracle\product\10.2.0\db_1\BIN>dir E:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\UDUMP
驱动器 E 中的卷是 E
卷的序列号是 38DC-C2C9
E:\ORACLE\PRODUCT\10.2.0\DB_1\ADMIN\ORCL\UDUMP 的目录
2014/08/29 14:56
2014/08/29 14:56
2014/08/29 14:56 6,458 orcl_ora_9220.trc ---> 生成的文件
1 个文件 6,458 字节
2 个目录 45,554,688,000 可用字节
③ 查看10046事件跟踪内容：
=====================
PARSING IN CURSOR #3 len=35 dep=0 uid=0 oct=50 lid=0 tim=281039119221 hv=1972993869 ad='1d4a43dc'
explain plan for select 1 from dual
END OF STMT
PARSE #3:c=0,e=32223,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=281039119218
BINDS #3:
=====================
PARSE ERROR #2:len=587 dep=1 uid=0 oct=2 lid=0 tim=281039135946 err=942 ---> (ORA-00942: 表或视图不存在)
insert into plan_table (statement_id, timestamp, operation, options,object_node, object_owner, object_name, object_instance, object_type,search_columns, id, parent_id, position, other,optimizer, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates, projection, time, qblock_name, object_alias, plan_id, depth, remarks, other_xml ) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36)
EXEC #3:c=0,e=16568,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=281039137106
ERROR #3:err=2402 tim=28104338 ---err=2402对应报错ORA-02402: 未找到 PLAN_TABLEWAIT #3: nam='SQL*Net break/reset to client' ela= 3 driver id=1111838976 break?=1 p3=0 obj#=-1 tim=281039137622
WAIT #3: nam='SQL*Net break/reset to client' ela= 61 driver id=1111838976 break?=0 p3=0 obj#=-1 tim=281039137920
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=281039138206
WAIT #3: nam='SQL*Net message from client' ela= 193 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=281039138936
=====================
分析：
plan_table[$] 说简单就是查看执行计划用的表，如库中没有该对象(或没该对象的权限),那估算的执行计划都是不可以用.
除本次实验’explain plan for ’ 还包括：
(使用Toad、PL/SQL Developer工具 、autotrace 且用法如下：
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SET AUTOT[RACE] OFF 停止AutoTrace --缺省模式
SET AUTOT[RACE] ON 开启AutoTrace，显示AUTOTRACE信息和SQL执行计划结果
SET AUTOT[RACE] TRACEONLY 仅显示执行计划和统计信息，执行SQL但不显示SQL =SET AUTOT[RACE] ON
SET AUTOT[RACE] ON EXPLAIN 开启AutoTrace，仅显示AUTOTRACE的EXPLAIN信息
SET AUTOT[RACE] ON STATISTICS开启AutoTrace，仅显示AUTOTRACE的STATISTICS信息
SET AUTOTRACE ON EXPLAIN ------SELECT就不执行SQL ，(dml 执行)，只显示执行计划(只显示优化器执行路径报告 )
SET AUTOTRACE ON STATISTICS -- 执行SQL，只显示统计信息
SET AUTOTRACE ON ---------执行SQL且 包含执行计划和统计信息)
**(plan_table 执行计划是估算的，从内存v$..视图得到的执行计划是真实的)
---> 3)如何解决报错
解决很简单,,报错说对象不存在，那么创建就好了 ，怎么创建呢，文章头部一说2个表对应的脚本是什么
1)生成PLAN_TABLE[$] 表
SQL> @?/rdbms/admin/utlxplan.sql ------>或者 @?/rdbms/admin/catplan.sql(包括grant select, insert, update, delete on PLAN_TABLE$ to PUBLIC;和create or replace public synonym PLAN_TABLE)
表已创建。
2)开启10046事件跟踪 看下操作内容.....
如上有如下命令是什么 在此不说了...SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
SQL> explain plan for select 1 from dual;
已解释。
SQL> alter session set events '10046 trace name context off';
会话已更改。
SQL>
3)查看10046事件跟踪
=====================
PARSING IN CURSOR #3 len=35 dep=0 uid=0 oct=50 lid=0 tim=283911944557 hv=1972993869 ad='1d4a43dc'
explain plan for select 1 from dual
END OF STMT
PARSE #3:c=0,e=733,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=283911944554
BINDS #3:
=====================
PARSING IN CURSOR #2 len=586 dep=1 uid=0 oct=2 lid=0 tim=283911946869 hv=2508123768 ad='1d4c90b8'insert into plan_table (statement_id, timestamp, operation, options,object_node, object_owner, object_name, object_instance, object_type,search_columns, id, parent_id, position, other,optimizer, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates, projection, time, qblock_name, object_alias, plan_id, depth, remarks, other_xml ) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36)
END OF STMT
PARSE #2:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=283911946867
=====================
PARSING IN CURSOR #4 len=41 dep=1 uid=0 oct=3 lid=0 tim=283911948464 hv=387538332 ad='1d4c65f0'
SELECT ORA_PLAN_ID_SEQ$.NEXTVAL FROM DUAL -- > plan_table[$]都会creates the plan_id sequence number.END OF STMT
总结：
utlxplan.sql 包含生成普通PLAN_TABLE表，catplan.sql包含生成PLAN_TABLE$基表和同义词，报告说明了文章头部说明的'PLAN_TABLE[$]creates the plan_id sequence number.'
---> 4 --> 1)若sys用户存在plan_table，plan_table$基表的公有同义词 也存在会优先选择哪个表呢？
1)查看对象ID
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 59895
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/事物级别)该表是[oncommitpreserverows]session级，而不是[oncommitdeleterows]事物级
SQL>
2)为准确判断优先性，清空 sys.plan_table表数据 ---若首先选择plan_table，plan_table普通表会记录数据..
SQL> truncate table plan_table;
表被截断。
SQL>
3)开启10046 事件跟踪查看
SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
SQL> explain plan for select 1 from dual;
已解释。
SQL> alter session set events '10046 trace name context off';
会话已更改。
SQL>
跟踪内容：
=====================
PARSING IN CURSOR #3 len =35 dep= 0 uid =0 oct= 50 lid=0 tim=289123971008 hv=1972993869 ad='1d4a43dc'
explain plan for select 1 from dual
END OF STMT
PARSE #3:c=0,e= 646,p=0 ,cr=0,cu= 0,mis=1 ,r=0,dep= 0,og=1 ,tim=289123971005
BINDS #3:
=====================
PARSING IN CURSOR #2 len =586 dep= 1 uid =0 oct= 2 lid=0 tim=289123972874 hv=2508123768 ad='1d4c90b8'
insert into plan_table (statement_id, timestamp, operation, options,object_node, object_owner, object_name, object_instance, object_type,search_columns, id , parent_id, position, other,optimizer, cost, cardinality , bytes, other_tag, partition_start, partition_stop, partition_id, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates, projection, time , qblock_name, object_alias, plan_id, depth , remarks, other_xml ) values(:1 ,:2,: 3,:4 ,:5,: 6,:7 ,:8,: 9,:10 ,:11,: 12,:13 ,:14,: 15,:16 ,:17,: 18,:19 ,:20,: 21,:22 ,:23,: 24,:25 ,:26,: 27,:28 ,:29,: 30,:31 ,:32,: 33,:34 ,:35,: 36)
END OF STMT
PARSE #2:c=0,e= 413,p=0 ,cr=0,cu= 0,mis=1 ,r=0,dep= 1,og=4 ,tim=289123972871
=====================
PARSING IN CURSOR #4 len =41 dep= 1 uid =0 oct= 3 lid=0 tim=289123974056 hv=387538332 ad='1d4c65f0'
SELECT ORA_PLAN_ID_SEQ$.NEXTVAL FROM DUAL
END OF STMT
4)查看plan_table 普通表是否有数据
E:\oracle\product\10.2.0\db_1\BIN>sqlplus.exe / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 8月 29 17:11:48 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select count(1) from plan_table;
COUNT(1)
----------
2
已用时间: 00: 00: 00.00
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE')); --> 查看执行计划
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行。
已用时间: 00: 00: 00.22
SQL>
总结：
当sys用户存在plan_table，plan_table$基表的公有同义词也存在，优先选择表而不是基于 基表创建的同义词...【表 -> 同义词】
---> 4 --> 2)若普通用户存在plan_table，plan_table$基表的公有同义词 也存在会优先选择哪个表呢？.... 【结论是：优先当前用户的plan_table，后选择同义词，若清楚的可以不看如下白色字体部分】
1)查看对象ID 和用户ID
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 59895
SYS PLAN_TABLE$ TABLE 59898
PUBLIC PLAN_TABLE SYNONYM 8709
SQL> select * from dba_users u where username in ('SYS','TEST');
USERNAME USER_ID PASSWORD ACCOUNT_STATUS
------------------------------ ---------- ------------------------------ --------------------
TEST 61 7A0F2B316C212D67 OPEN
SYS 0 8A8F025737A9097A OPEN
SQL>
2) 登录test用户进行测试
SQL> conn test/test
已连接。
SQL> @?/rdbms/admin/utlxplan.sql
表已创建。
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 59895
SYS PLAN_TABLE$ TABLE 59898
PUBLIC PLAN_TABLE SYNONYM 8709
TEST PLAN_TABLE TABLE 59901
3) 开启10046 事件跟踪 查看情况
SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
SQL> explain plan for select 1 from dual;
已解释。
SQL> alter session set events '10046 trace name context off';
会话已更改。
SQL>
跟踪内容
=====================
PARSING IN CURSOR #3 len=35 dep=0 uid=61 oct=50 lid=61 tim=285666459545 hv=1972993869 ad='1d4a43dc'
explain plan for select 1 from dual
END OF STMT
PARSE #3:c=0,e=884,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=285666459542
BINDS #3:
=====================
PARSING IN CURSOR #2 len=586 dep=1 uid=61 oct=2 lid=61 tim=285666461535 hv=2508123768 ad='1d4c90b8'
insert into plan_table (statement_id, timestamp, operation, options,object_node, object_owner, object_name, object_instance, object_type,search_columns, id, parent_id, position, other,optimizer, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates, projection, time, qblock_name, object_alias, plan_id, depth, remarks, other_xml ) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36)
END OF STMT
PARSE #2:c=0,e=455,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=285666461533
=====================
PARSING IN CURSOR #4 len=41 dep=1 uid=0 oct=3 lid=0 tim=285666462709 hv=387538332 ad='1d4c65f0'
SELECT ORA_PLAN_ID_SEQ$.NEXTVAL FROM DUAL
END OF STMT
查看表数据情况
SQL> select count(1) from plan_table;
COUNT(1)
----------
2
总结：
普通用户存在plan_table，plan_table$基表的公有同义词,会优先选择普通用户存在的表.【表 -> 同义词】
---> 5 )2个PLAN_TABLE【$】表创建路径/脚本详情
SQL> ! cat $ORACLE_HOME/rdbms/admin/catplan.sql --> PLAN_TABLE$
......省略.....
create global temporary table plan_table$
(
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_alias varchar2(65),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
depth numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
other_xml clob,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000),
projection varchar2(4000),
time numeric,
qblock_name varchar2(30)
) on commit preserve rows
/
Rem
Rem Add necessary privileges and make plan_table$ the default for
Rem everyone
Rem
grant select, insert, update, delete on plan_table$ to public --> 允许DML操作
/
create or replace public synonym plan_table for plan_table$ ---创建同义词..生成执行计划时后台会找plan_table表而不是plan_table$/
......省略.....
SQL> ! cat $ORACLE_HOME/rdbms/admin/utlxplan.sql --> PLAN_TABLE
Rem This is the format for the table that is used by the EXPLAIN PLAN
Rem statement. The explain statement requires the presence of this
Rem table in order to store the descriptions of the row sources.
create table PLAN_TABLE (
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_alias varchar2(65),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
depth numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000),
projection varchar2(4000),
time numeric,
qblock_name varchar2(30),
other_xml clob);
总结：
1、【plan_table、基于PLAN_TABLE$基表创建同义词】都可以理解是查看执行计划用的表，如库中没有该对象(或没该对象的权限),那估算的执行计划都是不可以用的；包括【explain plan for /SET AUTOT[RACE]...等】
2、plan_table普通表提交其他会话可以看数据【session1:commit，session2可查看数据】、同义词PLAN_TABLE session级临时表【session1:commit，session2不可查看数据】
具体查看：http://blog.itpub.net/28602568/viewspace-1260334/
3、同实例同owner，一个为基于PLAN_TABLE$基表创建的同义词、一个是PLAN_TABLE 优先于真是表.
祝好~
1533
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
