使用10046 event跟踪session（oracle 10046跟踪指定session）

----------------------------

10046event是oracle用于系统性能分析时的一个最重要的事件。当激活这个事件后，将通知oracle kernel追踪会话的相关即时信息，并写入到相应trace文件中。这些有用的信息主要包括sql是如何进行解析，绑定变量的使用情况，会话中发生的等 待事件等。10046event 可分成不同的级别（level），分别追踪记录不同程度的有用信息。对于这些不同的级别，应当注意的是向下兼容的，即高一级的trace信息包含低于此级 的所有信息。

10046event的追踪级别大致有：

level 1：跟踪sql语句，包括解析、执行、提取、提交和回滚等。

level 4：包括变量的详细信息

level 8：包括等待事件

level 12：包括绑定变量与等待事件

其中，level 1相当于打开了sql_trace

10046event的启用和关闭：

前提条件：（先确保要event的会话环境符合条件）

1、必须确保timed_statistics为TRUE，这个参数可以在会话级上进行修改。

2、为了确保trace输出能够完整进行，还要调整此会话对trace文件大小的限制，一般将此限制取消，即将max_dump_file_size设置为UNLIMITED，或者设置为一个很大的阙值。

在满足了上述条件后，就可以启用10046event对会话进行后台跟踪了。

这里还有几种方式来启用10046event：

一种是在当前会话启用event，可以利用alter session + 事件名称 + level，

如：sql>alter session set event ‘10046 trace name context forever, level 12’;

另外一种是启用别的会话进行event跟踪，可以利用oracle提供的dbms_system来完成。

如：sql>exec dbms_system.set_ev(sid,serial#,10046,12,’’);

注意：

sql>exec dbms_system.set_sql_trace_in_session(sid,serial#,true);

相当于打开了sql_trace。

event的关闭：

可以在通过下面的语句来关闭当前会话的event：

sql>alter session set event ‘10046 trace name context off’;

也可以利用dbms_system包来关闭某个会话的event：

sql>execute dbms_system.set_ev(sid,serial#,0,’’);

这里应当值得一提的是，TRACE将消耗相当的系统资源，因此我们在使用TRACE的时候应当慎重。对于正式的系统，应当只在必要的时候进行TRACE操作，并且应当及时关闭。

当利用事件trace完当前或某个session后，接下来我们的工作就是找到oracle生成的trace了。Oracle的初始化文件中user_dump_dest参数的设置将决定trace文件的生成位置。

从trace文件中查找和发现有用的信息，然后寻找必要的性能调整点并进行相应的调整：

大部分情况下，通过10046事件trace到文件里的信息包含了此会话中存在的性能问题，可以根据trace到的等待事件、SQL语句执行情况以及绑定变量的使用情况来进行分析和查找。

oracle提供了一个工具tkprof来对trace文件进行格式的翻译，以便trace文件中记录的信息能够被我们容易掌握和获取。

小知识：

检查当前会话的sql_trace状态或级别：

SQL>set serveroutput on

2 declare i_event number;

3 begin

3 sys.dbms_system.read_ev(10046,i_event);

4 dbms_output.put_line(‘the session sql_trace level is: ‘||i_event);

5 end;

6 /

the session sql_trace level is: 12

PL/SQL 过程已成功完成。

oracle tkprof使用小结

1.chechk init.ora

timed_statistics=true

TOP查出最耗资源的PID

2.SQL> select s.sid,s.serial# from v$session s,v$process p

where s.paddr=p.addr and p.spid='17397';

3.如果需要在session级别上设置trace，可以在sqlplus中使用下列语句：

SQL> alter session set sql_trace=true;

或者SQL> execute dbms_session.set_sql_trace(TRUE);

会话已更改。

4.如果要在PL/SQL中对session级别上设置trace，可以使用dbms_session这个包：

SQL>exec dbms_system.set_sql_trace_in_session(sid，serial#,true);

PL/SQL 过程已成功完成。

5.在user_dump_dest下找到该trc文件,文件最大容量由 max_dump_file_size决定

6.使用tkprof生成相关文件,tkprof放在$ORACLE_HOME/bin目录下，如无法执行请检查环境变量和PATH

tkprof erptest_ora_27576.trc session.txt explain=system/manager aggregate=yes sys=no waits=yes sort=fchela

相关说明:

sys=no：表示阻止所有以sys用户执行的sql被显示出来，默认为YES

aggregate=yes|no 若用户指定AGGREGATE=NO，TKPROF将不会对相同SQL文本的多个用户进行汇总

waits=yes|no Record summary for any wait events found in the trace file.

CALL： 每次SQL语句的处理都分成三个部分（Parse，Execute,Fetch）

Parse： 这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。

Execute： 这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作,这步就只是确定选择的记录。

Fetch： 返回查询语句中所获得的记录，这步只有select语句会被执行。

COUNT: 这个语句被parse、execute、fetch的次数。

CPU： 这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。

ELAPSED： 这个语句所有消耗在parse、execute、fetch的总的时间。

DISK： 从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正在从缓存中读取的数据而不是从磁盘上读取的数据。

QUERY： 在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。 一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。

CURRENT：在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取 buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。

ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，

对于insert、update、delete操作，返回记录则是在execute这步。

tkprof产生出来的文件示例:

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 5 0.00 0.00 0 0 0 0

Execute 7 0.10 0.14 0 0 0 0

Fetch 12 0.00 0.06 5 38 0 9

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 24 0.11 0.21 5 38 0 9

问题判断：

1. query+current/rows 平均每行所需的block数，太大的话（超过20）SQL语句效率太低

2. Parse count/Execute count parse count应尽量接近1，如果太高的话，SQL会进行不必要的reparse。

要检查Pro*C程序的MAXOPENCURSORS是不是太低了，或不适当的使用的RELEASE_CURSOR选项

3. rows Fetch/Fetch Fetch Array的大小，太小的话就没有充分利用批量Fetch的功能，

增加了数据在客户端和服务器之间的往返次数。在Pro*C中可以用prefetch=NN,Java/JDBC中可调用SETROWPREFETCH,

在PL/SQL中可以用BULK COLLECT,SQLPLUS中的arraysize(缺省是15)

4. disk/query+current 磁盘IO所占逻辑IO的比例，太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)

5. elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源

6. cpu　OR　elapsed 太大表示执行时间过长，或消耗了大量的CPU时间，应该考虑优化

7.　执行计划中的Rows 表示在该处理阶段所访问的行数，要尽量减少