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

时间：2021-11-21 11:39:41
SQL_TRACE/10046 事件是 Oracle 提供的用于进行 SQL 跟踪的手段，在日常的数据库问题诊断和解决中是非常常用的方法。但其生成的trace文件需要tkprof工具生成一个可供人类阅读的格式才可。
TKPROF用法请参考［terry笔记］Oracle会话追踪（二）：TKPROF
会话追踪可以追踪本会话、其他会话以及整个数据库，但是追踪整个数据库会对数据库的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平台完蛋）
［terry笔记］Oracle会话追踪（一）：SQL_TRACE&EVENT 10046
［terry笔记］Oracle会话追踪（一）：SQL_TRACE&EVENT 10046
  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。 

相关文章