存储过程调优之“10046”事件
一、前言
前段时间应需求，写存储过程，以满足避免在大数据量的原始表中进行直接的查询工作。从而生成一张中间表，用于以后各个维度的报表统计
从而提高每张报表的查询效率。久而久之，随着存储过程越来越多，每天的任务耗时也越来越大，从而不得不考虑对存储过程进行优化。
二、"10046"事件
Oracle的10046事件，它可以跟踪应用程序所执行的SQL语句，从而得到每条SQL的解析次数，执行次数，CPU使用时间，每条SQL中每个部位的耗时等。这样，我们
就可以根据这些信息来分析、定位数据库的性能问题。
10046event是oracle用于系统性能分析时的一个最重要的事件，当激活这个事件后，将通知oracle kernel追踪会话的相关即时信息，并写入相应的trace文件中。这些
信息包括SQL的解析，绑定变量的使用情况，会话中发生的等待时间以及会罗列出主要耗时的部分等。
10046event有不同的级别（level），分别追踪记录不同程度的信息。需要注意的是，级别是向下兼容的 即高一级别生成的trace所包含的信息包含低一级别的所有信息，
追踪级别大致有：
level 1：跟踪sql语句，包括解析、执行、提取、提交和回滚等。
level 4：包括变量的详细信息。
level 8：包括等待事件。
level 12：包括绑定变量与等待事件。
其中，level 1相当于打开了sql_trace。
(Tips) 向下兼容。
三、10046event 追踪最小化例子
命令行/plsql 键入如下：
alter session set tracefile_identifier='10046';-----------------------设置追踪标识符
alter session set events '10046 trace name context forever, level 12';----------------开启追踪，并设置追踪级别
select * from ALL_OBJECTS; -----------------所要追踪的SQL语句
alter session set events '10046 trace name context off';------------关闭追踪
执行完后，即所有会话都结束后，oracle将会生成一个orcl_ora_XXXX_10046.trc 的文件，根据个人目录不同，会生成在不同的
目录当中，我们可以用locate命令来进行查询
得到这个文件后，我们再用tkprof这个命令，对该文件进行格式化，便于我们的阅读。
如：tkprof trace\orcl_ora_xxxx_10046.trc 10046.txt sys=no sort=prsela,exeela,fchela
四、解析trace文件
我们打开10046.txt，对于初学者来说，并不能好好的去分析包含的信息，因为其中包含了许多“名词”，下面将会对此进行介绍
1）摘录第一部分，SQL语句的执行情况总览
select *
from
ALL_OBJECTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 10.94 10.68 222186 222957 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 10.94 10.68 222186 222957 0 1
关于统计表格的标题信息中count、cpu、elapsed、disk、query、current和rows的说明在该trace文件的最前端有一个简要的说明，这里再分别赘述一下。
count ：查询在此阶段执行的次数；
cpu ：该查询在此阶段的CPU时间量，以毫秒为单位；
elapsed ：花费在此阶段上的挂钟时间，该值比cpu值大的时候，表明存在等待事件；
disk ：执行物理I/O次数；
query ：在意一致性检索方式获得块时，执行逻辑I/O次数；
current ：逻辑I/O次数；
rows ：此阶段，被处理或受影响的行数。
关于第一列的赘述：
Parse ：软编译和硬编译次数；
Execute ：在open和execute语句中完成的内容;
Fetch ：select中会有数据显示，在update语句中不会有数据显示。
2）摘录运行环境信息
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 51
第一行的“0”表示查询使用的是软解析（soft parse）。
优化模式是：ALL_ROWS
使用最后一行的用户ID可以获得执行时的会话信息。获得用户信息可以通过下面的SQL语句完成。
sys@ora10g> select * from all_users where user_id = 51;
USERNAME USER_ID CREATED
------------------------------ ---------- -------------------
SEC 51 2015-02-15 13:04:03
3）摘录执行计划信息
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=222957 pr=222186 pw=0 time=10686023 us)
100000000 INDEX FAST FULL SCAN PK_T (cr=222957 pr=222186 pw=0 time=100000562 us)(object id 45619)
有趣发现：通过第二行可以得到这个t表的数据量，这里显示结果是1亿。
“解剖”上面出现的几个重要参数：
cr=222957 -- 一致性读取
pr=222186 -- 物理读取
pw=0 -- 物理写
time=100000562 us -- 占用时间，单位：微妙 百万分之一秒
4）摘录等待事件
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 0.00 0.00
db file scattered read 14249 0.00 1.10
db file sequential read 59 0.00 0.00
通过这段等待事件的描述，可以清楚的得到在执行SQL语句的过程中都出现了哪些引人注目的等待事件。比如上面显示出的“db file scattered read”和“db file sequential read”信息，如果此类信息在生产环境中大量出现，就需要重点深入分析和研究了。
五、总结
对于10046事件，本身是非常消耗资源，对于存储过程，如果对此进行设置跟踪，会增加该存过的耗时，因此在使用过程中应避免一直启用追踪。但对于存储过程的调优，其包含的各种信息是非常有助于对整个存过进行有效的分析，对于10046扩展追踪,非常适合于对那些把很多业务逻辑写入到存储过程中的软件调优
