SQL_Trace/10046事件

shuaiwei588

　　SQL_TRACE是Oracle提供的用于进行SQL跟踪的手段。在日常的数据库问题诊断和解决中，SQL_TRACE是非常常用的方法。
　　当某个应用比较慢时，可以使用SQL_TRACE或者10046事件对其应用跟踪。然后可以通过对此跟踪信息的分析，找出应用比较慢的原因。
　　SQL_TRACE：
　　SQL_TRACE可以作为初始化参数在整个数据库启用，也可以通过命令行方式在具体session启用。
　　数据库级别：
　　在整个数据库启用SQL_TRACE会导致所有进程的活动被跟踪，包括后台进程及所有用户进程。效率缓慢，慎用。
--第1种　　
sql_trace =true
　　

　　
--第2种
　　
alter system set sql_trace =true scope=spfile;
　　Session级别：
　　跟踪当前session。
--开始session级trace跟踪　　
alter session set sql_trace=true;
　　

　　
SELECT to_number('asdf') FROM dual;
　　

　　
--结束trace跟踪
　　
alter session set sql_trace=false;
　　使用存储过程：
　　可以跟踪其他用户的进程，而并非当前用户。
--DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION　　
--用以查询sid,serial#
　　
SELECT Sid, Serial#, Username
　　
  FROM V$session
　　
WHERE Username IS NOT NULL;
　　

　　
--开始
　　
Sys.Dbms_System.Set_Sql_Trace_In_Session(Sid       => &Sid,
　　
                                         Serial#   => &Serial#,
　　
                                         Sql_Trace => TRUE);
　　

　　
--结束
　　
Sys.Dbms_System.Set_Sql_Trace_In_Session(Sid       => &Sid,
　　
                                         Serial#   => &Serial#,
　　
                                         Sql_Trace => FALSE);
　　10046：
　　10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强。
　　10046事件可以设置以下四个级别:
　　1 - 启用标准的SQL_TRACE功能,等价于sql_trace
　　4 - Level 1 加上绑定值(bind values)
　　8 - Level 1 + 等待事件跟踪
　　12 - Level 1 + Level 4 + Level 8
　　类似sql_trace，10046事件可以在全局设置，也可以在session级设置。
　　数据库级：
　　此设置对所有用户的所有进程生效、包括后台进程。
　　在参数文件中增加:
Event="10046 trace name context forever, level 12"　　Session级：
　　通过alter session的方式修改，需要alter session的系统权限。
--开始　　
alter session set events '10046 trace name context forever, level 12';
　　

　　
--结束
　　
alter session set events '10046 trace name context off';
　　使用存储过程：
　　可以跟踪其他用户的进程，而并非当前用户。
--Dbms_System.Set_Ev　　

　　
--用以查询sid,serial#
　　
SELECT Sid, Serial#, Username
　　
  FROM V$session
　　
WHERE Username IS NOT NULL;
　　

　　
--开始
　　
Sys.Dbms_System.Set_Ev(Si => &Sid,
　　
                       Se => &Serial#,
　　
                       Ev => &Event, --10046
　　
                       Le => &Level, --1,4,8,12
　　
                       Nm => &Name --随便取名
　　
                       );
　　

　　
--结束
　　
Sys.Dbms_System.Set_Ev(Si => &Sid,
　　
                       Se => &Serial#,
　　
                       Ev => &Event, --10046
　　
                       Le => 0, --0为不跟踪
　　
                       Nm => &Nm);
　　获取trace文件名：
　　获取默认文件名SQL如下：
--第1种　　
SELECT u_Dump.Value || '/' || Lower(Db_Name.Value) || '_ora_' ||
　　
       V$process.Spid ||
　　
       Nvl2(V$process.Traceid, '_' || V$process.Traceid, NULL) || '.trc' "Trace File"
　　
  FROM V$parameter u_Dump
　　
CROSS JOIN V$parameter Db_Name
　　
CROSS JOIN V$process
　　
  JOIN V$session
　　
    ON V$process.Addr = V$session.Paddr
　　
WHERE u_Dump.Name = 'user_dump_dest'
　　
   AND Db_Name.Name = 'db_name'
　　
   AND V$session.Audsid = Sys_Context('userenv', 'sessionid');
　　

　　
--第2种
　　
SELECT d.Value || '/' || Lower(Rtrim(i.Instance, Chr(0))) || '_ora_' ||
　　
       p.Spid || '.trc' AS "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;
　　eg:
　　/data03/DEV4/db/9.2.0/admin/DEV4_mistest/udump/dev4_ora_9359834.trc
　　读取当前session设置的参数：
　　当通过alter session的方式设置了sql_trace,这个设置需要通过dbms_system.read_ev来获取：
DBMS_SYSTEM.READ_EV(　　
iev IN BINARY INTEGER, --Event number; usually between 10000 and 10999
　　
oev OUT BINARY INTEGER --Level at which the event is set; level=0 means the event is disabled
　　
);