你是否想知道一句sql语句如何执行,它是否走索引,是否采用不同得驱动表,是否用nestloop join,hash join…..?这一切
对你是否很神秘呢?或许你会说execution plan能看到这些东西,但是你是否清楚execution plan是如何得到?这篇文章就
是给出了隐藏在execution plan底下的具体实现。
10053跟踪事件:可以提供关于执行计划的信息,可以帮助你,分析一条SQL语句,读取执行计划,产生跟踪文件 trace file
。oracle提供的用于跟踪sql语句成本计算的内部事件,它能记载CBO模式下oracle优化器如何计算sql成本,生成相应的执行
计划。
下面我介绍打开跟踪文件的3种方法
方法一:
对当前会话开启和关闭事件
alter session set events '10053 trace name context forever'; --打开10053事件
中间填写要跟踪的句子...
alter session set events '10053 trace name context off'; --关闭10053事件
select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' || case
when e.value is not null then
'_' || e.value
end trace
from v$process a, v$session b, v$parameter c, v$instance d, v$parameter e
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
and e.name = 'tracefile_identifier';
--查看跟踪文件的位置
方法二:
对整个数据库开启和关闭此事件,需要注意的是此设置不会立即生效,而是对后续创建的会话起作用
alter system set events '10053 trace name context forever'; --打开10053事件
中间填写要跟踪的句子
alter system set events '10053 trace name context off'; --关闭10053事件
方法三:
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug event 10053 trace name context forever, level 1; --开启
select * from TEST.testloop where A=1111; --中间填写要跟踪的SQL
oradebug event 10053 trace name context off; --关闭
SQL> oradebug tracefile_name --查看刚才生成的跟踪文件的位置
d:\oracle\product\10.2.0\admin\icare\udump\icare_ora_5388.trc
当10053事件打开之后,查询优化器产生一个跟踪文件,文件中包含大量当前正在进行的工作信息,里面有初始化参数定义的
运行环境,系统统计,对象统计以及为了发现最优执行计划而进行的评估
每个服务器进程会把它所硬解析的SQL语句相关的所有数据记录在自己的跟踪文件中,这不仅意味着一个跟踪文件可以包含多
条SQL语句的信息,也可推知有几个会话开启了此事件就会有几个跟踪文件产生
跟其他跟踪事件不同,10053提供了两个跟踪级别,但是级别2的跟踪信息比级别1少(其他跟踪事件如10046跟踪级别越高信
息越多),跟踪信息将被记录到user_dump_dest目录底下。注意,要实现跟踪必须满足两个条件:sql语句必须被hard parse
并且必须使用CBO优化器模式。如果sql语句已经被parse过,那么10053不生成跟踪信息。如果你使用RULE优化器,那么10053
也不会生成跟踪信息。
跟踪内容
跟踪文件包括6部分:
Sql语句
优化器相关参数
基本统计信息
基本表访问成本
综合计划
特殊功能的成本重计算
分析小结
分析10053的过程,一般是从跟踪文件的底部往上看,再到感兴趣的区域。感兴趣的区域就是那些属于看起来有问题的执
行计划的区域。
在这里列举进行10053跟踪文件分析的步骤:
1) 从文件末尾开始
确认你感兴趣的sql语句存在于跟踪文件中。如果跟踪文件中有EXPLAIN PLAN,编辑一下跟踪文件尽量便于阅读。
2)查看所选执行计划的最终成本。
3)查看产生最终成本的连接顺序
4)查看感兴趣的引起成本的连接顺序部分
5)查看连接顺序产生成本的连接类型
6)检查在步骤5中找到的连接类型的成本
a)确认用到何种访问路径
b)检查其他被拒绝的访问路径(仅适用于嵌套循环连接,在对内部行集的访问路径中,有几个访问路径产生成本)
详细分析
1.从结尾开始,从执行计划开始
在10053中获得准确的执行计划输出,很重要。有些信息在10053中会丢失,我们需要使用执行计划来得出成本计算是如
何进行的结论。有时候我们会使用执行计划来调查10053或者检查我们的分析。