实验:
create table a as select * from dba_objects;
create table b as select * from dba_objects;
我们跑这个SQL:
select count(distinct owner), count(distinct object_id)
from a
where a.owner in (select owner from b);
看看ORACLE会怎么改写这个SQL
重新打开一个SQLPLUS
alter session set tracefile_identifier='fuck';
这个命令是指,TRACE文件要带一个fuck的字样
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
explain plan for select count(distinct owner), count(distinct object_id)
from a
where a.owner in (select owner from b);
ALTER SESSION SET EVENTS '10053 trace name context off';
select c.value || '/' || d.instance_name ||'_ora_' || a.spid || '_' || a.traceid || '.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest';
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest';
通过这个SQL来找到文件路径
10053是用来ORACLE把原始的SQL改成什么样了。
有两点需要注意:一使用10053时,问题SQL一定要用EXPLAIN PLAN FOR
然后10053最重要的就只看一部分:
Final query after transformations:---最重要的
如:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(DISTINCT "A"."OWNER") "COUNT(DISTINCTOWNER)",COUNT(DISTINCT "A"."OBJECT_ID") "COUNT(DISTINCTOBJECT_ID)" FROM "SCOTT"."B" "B","SCOTT"."A" "A" WHERE "A"."OWNER"="B"."OWNER"
kkoqbc: optimizing query block SEL$5DA710D3 (#1)
SELECT COUNT(DISTINCT "A"."OWNER") "COUNT(DISTINCTOWNER)",COUNT(DISTINCT "A"."OBJECT_ID") "COUNT(DISTINCTOBJECT_ID)" FROM "SCOTT"."B" "B","SCOTT"."A" "A" WHERE "A"."OWNER"="B"."OWNER"
kkoqbc: optimizing query block SEL$5DA710D3 (#1)
这个明明是半连接,被ORACLE 内部转化改成了内联接了。这个做内联就是个笛卡尔积。
create table test as select * from dba_objects;
设置trace 名字,方便查找
alter session set tracefile_identifier='robinson';
开启trace 12级
alter session set events '10046 trace name context forever, level 12';
执行SQL
select count(*) from test;
关闭TRACE
alter session set events '10046 trace name context off';
找出TRACE文件在哪个目录 :
select c.value || '/' || d.instance_name ||'_ora_' || a.spid || '_' || a.traceid || '.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
/
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
/
tkprof ---格式化TRACE的工具
10046 trace 最最有用的 是什么?
1. 查SQL产生的等待事件
2、10046 用来 发现 隐含 的 递归调用的 sql
要想 10046 trace 出来 有 等待 事件 必须?????
alter system flush buffer_cache;
所以10046请在测试环境下用,生产环境用是找死。
10053 是用来干嘛的???
研究 CBO,看SQL实际最终是怎样的。最终的 sql 的 final,可以用来检查 cbo 的bug
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10053 trace name context off';
ALTER SESSION SET EVENTS '10053 trace name context off';