［terry笔记］Oracle会话追踪（一）：SQL_TRACE&EVENT 10046

 

SQL_TRACE/10046 事件是 Oracle 提供的用于进行 SQL 跟踪的手段，在日常的数据库问题诊断和解决中是非常常用的方法。但其生成的trace文件需要tkprof工具生成一个可供人类阅读的格式才可。

TKPROF用法请参考［terry笔记］Oracle会话追踪（二）：TKPROF

http://www.cnblogs.com/kkterry/p/3279288.html

会话追踪可以追踪本会话、其他会话以及整个数据库，但是追踪整个数据库会对数据库的cpu、io造成很大负载，并不常用，故这里便不再讨论有关追踪整个数据库的例子以及过程。

 

一、SQL_TRACE

为了使SQL_TRACE效果更明显:
初始化参数TIMED_STATISTICS 设置为 True,否则一些重要信息不会被收集，该参数默认即为true。
设置 MAX_DUMP_FILE_SIZE 该参数设置跟踪文件的大小限制,从9i开始,该参数默认值为 unlimited。

 

1.追踪当前会话

启动SQL_TRACE:

alter session set sql_trace=true;

这时可以执行sql，其过程已被追逐。

关闭SQL_TRACE:

alter session set sql_trace=false;

 

2.追踪其他会话，利用dbms_system.set_sql_trace_in_session包

首先查询当前系统的会话：

select sid,serial#,username from v$session;

启动SQL_TRACE：

exec dbms_system.set_sql_trace_in_session(sid,serial#,true)

等待一段时间，如果确定某个功能或模块存在问题,可以在此期间有意识地调用,以确保可以捕获问题代码。

关闭SQL_TRACE：

exec dbms_system.set_sql_trace_in_session(sid,serial#,false)

 

 

二、10046事件

10046 事件是 Oracle 提供的内部事件,是对 SQL_TRACE 的增强，可以设置以下 4 个级别:

Level 1:启用标准的 SQL_TRACE 功能,等价于 SQL_TRACE。

Level 4:等价于 Level 1 + 绑定值(bind values)。

Level 8:等价于 Level 1 + 等待事件跟踪。

Level 12:等价于 Level 1 + Level 4 + Level 8。

类似 SQL_TRACE,10046 事件可以在全局设置,也可以在 session 级设置。

 

1.追踪当前会话

启动10046:

alter session set events '10046 trace name context forever';
alter session set events '10046 trace name context forever, level 8';

关闭10046:

alter session set events '10046 trace name context off';

 

2.追踪其他会话，利用dbms_system.set_ev包,包的选项分别是（sid,serial#,event,level,username）

首先查询当前系统的会话：select sid,serial#,sid from v$session;

启动10046:

exec dbms_system.set_ev(sid,serial#,10046,8,'username');

关闭10046:

exec dbms_system.set_ev(sid,serial#,10046,0,'username'); 

 

三、获取追踪文件位置(其实就在user_dump_dest下，进去ls -ltr就行了)：

其trace文件的命名方式SID_ora_PID.trc

SELECT d.value 
       ||'/' 
       ||Lower(Rtrim(i.INSTANCE, Chr(0))) 
       ||'_ora_' 
       ||p.spid 
       ||'.trc' trace_file_name 
FROM   (SELECT p.spid 
        FROM   v$mystat m, 
               v$session s, 
               v$process p 
        WHERE  m.statistic# = 1 
               AND s.sid = m.sid 
               AND p.addr = s.paddr) p, 
       (SELECT t.INSTANCE 
        FROM   v$thread t, 
               v$parameter v 
        WHERE  v.name = 'thread' 
               AND ( v.value = 0 
                      OR t.thread# = To_number(v.value) )) i, 
       (SELECT value 
        FROM   v$parameter 
        WHERE  name = 'user_dump_dest') d; 

 

四、追踪的几种方式：

1.追踪自己，这个就不用说什么了，为了检查某些特殊sql，可以用这个办法。

2.追踪他人session更加有效果，但是如何定位有问题的session？

  1）top，很容易查看到当前使用资源更多的连接以及PID号（win平台完蛋）

  2）通过PID查找到其sid、serial#

select s.sid,s.serial# from v$session s,v$process p where s.paddr=p.addr and p.spid='14483';

 

  3)通过sid,serial#进行trace，然后输出tkprof文件即可

 

3.在环境没有人用的时候（加班。。），只让某几个session连接进入数据库（就是v$session只有几个连接），然后点击前台应用特定的模块进行trace。