【精】10046事件追踪

ora-10046

昨天的文章当中我们说了说如何产生执行计划几种手段，包括set autotrace / explan plan for / dbms_xplan.display / awrsqrpt.sql / hint (/*+ gather_plan_statistic */ )，其实还有一种方法我没有说到，就是ora-10046。我觉得这个应该单独放出来说一下。我在之前一直不知道这个玩意有啥用，因为确实是经验不足，用到的特别少，而且我觉得用起来不是特别方便，而且有可能导致数据库在做10046的时候性能直线下降。但是这个工具确实可以直接了当的定位到数据库的一些性能问题上，说实话是dba的一个必备的工具。而且，在《ORACLE工作笔记这本书当中》杨建荣老师酷爱使用这个去分析数据库性能表现。下面我们就说一说10046。

10046事件是SQL_TRACE的扩展，SQL_TRACE 是数据库当中的一个参数，该参数如果开启会在日志文件当中产生一个.trc的日志文件，这个文件捕获了数据库的操作变化活动。

而10046事件分为如下几个追踪级别：

0级：sql_trace=fasle

1级：sql_trace=true

4级：1级+绑定变量

8级：4级+等待事件

12级：4级+8级

我们先简单的实验看一下：

alter session set events '10046 trace name context forever , level 12 ' ; --开启trace跟踪

select * from scott.dept t1 ,scott.emp t2 where t1.deptno=t2.deptno;

alter session set events '10046 trace name context off'; --关闭trace跟踪

这个时候我们这个.trc，文件已经生成，查看最新的.trc文件，该文件的目录通过参数：user_dump_dest去查看

show parameter user_dump_dest

查看对应的日志使用：

select d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name

from

(

select p.spid

from sys.v$mystat m,sys.v$session s,sys.v$process p

where m.statistic# = 1

and s.sid = m.sid

and p.addr = s.paddr

) p,

(

select t.instance

from sys.v$thread t,sys.v$parameter v

where v.name = 'thread'

and ( v.value = 0 or t.thread# = to_number(v.value) )

) i,

(

select value from sys.v$parameter where name = 'user_dump_dest'

) d ;

我们查看对应的日志，找到对应的重要的信息：

我们可以看到大量的parse ,exec ,wait,fetch ,stat。这些都是对游标的操作，stat后面显示的一些执行计划，但是看起来好像并不是很直观，我们现在需要一个工具将日志的格式转换一下。

这个工具就是tkprof，我们通过tkprof格式化我们的trace日志。

我们先看一下这个工具的用法

explain ：参数是获取执行计划的，用法是explain plan的方法。在trace 当中找到sql语句，并且解析对应的执行计划。

table ：指定生成执行计划的表，这个参数和explain共同使用。

print ：限制输出文件生成的sql语句的数量，默认的是没有限制的。

insert ：生成sql语句的脚本，指定对应的文件名即可。

sort ：将输出的sql语句可以按照对应的选项进行排序。

aggregate： 是否合并相同的sql。

具体步骤：

tkprof orcl_ora_41142.trc 10046_new sys=no sort=pesela,exeela,fchela

这样会生成一个新的文件 10046_new.prf

vi 10046_new.prf

我们截取到对应sql的执行过程。我们可以看到产生了对应语句的执行计划。

count：执行的数据库调用次数

cpu：处理数据花费的cpu时间 （s）

elapsd：数据花费cpu的总时间，有cpu时间和等待时间。（s）

disk：物理读数量。

query：一致性模式读取的数据块数，select 。

current：当前操作读取的数据块数，insert ,update,delete。

rows：当前操作处理的数据行数。

misses in library cache during parse:表示当前的sql语句执行计划是否在share pool 当中命中，1为没有，进行了硬解析。0表示命中，执行软解析。

optimizer mode: 当前优化器模式

parsing user id：当前处理的用户

number of plan statistics captured ：统计信息的捕获数

其优点是可以看出对应的sql等待事件；

可以看出处理的行数和物理读；

看出解析时间和执行时间；

可以跟踪到整个程序包；

唯一麻烦是执行步骤上特别繁琐，看不到表被访问了多少次；

10046感觉上是比sql_trace上在控制上更好限制，有着更灵活的数据获取能力。

THAT'S ALL

BY CUI PEACE !!!!