Oracle sql trace
一.SQL_TRACE说明
1.1.在全局启用
在参数文件(pfile/spfile)中指定:sql_trace =true
1.2.在当前session级设置
启用当前session的跟踪:
alter session set sql_trace=true;
结束跟踪:
alter session set sql_trace=false;
1.3.跟踪其他用户进程
跟踪其他用户的进程，可以通过Oracle提供的系统包DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION来完成.SET_SQL_TRACE_IN_SESSION过程序要提供三个参数:
SQL> desc dbms_system
...
PROCEDURE SET_SQL_TRACE_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
SQL_TRACE BOOLEAN IN
...
通过v$session我们可以获得sid、serial#等信息:
SQL> select sid,serial#,username
2 from v$session
3 where username is not null;
SID SERIAL# USERNAME
---------- ---------- -------------
8 2041 SYS
9 437 SCOTT
启用跟踪:
exec dbms_system.set_sql_trace_in_session(9,437,true)
停止跟踪:
exec dbms_system.set_sql_trace_in_session(9,437,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级设置。
2.1.在全局设置
在参数文件中增加:
event="10046 trace name context forever,level 12"
此设置对所有用户的所有进程生效、包括后台进程.
2.2 对当前session设置
通过alter session的方式修改，需要alter session的系统权限:
执行跟踪:
SQL> alter session set events '10046 trace name context forever';
SQL> alter session set events '10046 trace name context forever, level 8';
结束跟踪:
SQL> alter session set events '10046 trace name context off';
2.3 对其他用户session设置
通过DBMS_SYSTEM.SET_EV系统包来实现:
SQL> desc dbms_system
...
PROCEDURE SET_EV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SI BINARY_INTEGER IN
SE BINARY_INTEGER IN
EV BINARY_INTEGER IN
LE BINARY_INTEGER IN
NM VARCHAR2 IN
...
其中的参数SI、SE来自v$session视图，查询获得需要跟踪的session信息:
select sid,serial#,username
from v$session
where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
8 2041 SYS
9 437 SCOTT
执行跟踪:
SQL> exec dbms_system.set_ev(9,437,10046,8,'SCOTT');
结束跟踪:
SQL> exec dbms_system.set_ev(9,437,10046,0,'SCITT');
三.与SQL Trace相关的参数
在打开10046时间的SQL Trace之前，要先设置好下面几个参数。
timed_statistics
是否收集与时间相关的统计信息，如果这个参数为FALSE的话，那么SQL Trace的结果基本没有多大的用处，默认情况下这个参数设置为TRUE。
max_dump_file_size
dump文件的大小，在一个很忙的系统上面做SQL Trace的话可能会生成很多的信息，因此最好在会话级别将这个参数设置成unlimited。
3.1 在全局设置
这些参数可以加载spfile中，让系统启动时自动做全局设置。
3.2 对当前session设置
ALTER SESSION SET timed_statistics=true
ALTER SESSION SET max_dump_file_size=unlimited
3.3 对其他用户session设置
SYS.DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION(:sid, :serial,'timed_statistics', true)
SYS.DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION(:sid, :serial,'max_dump_file_size', unlimited)
四.获取跟踪文件
以上生成的跟踪文件位于user_dump_dest目录中，位置及文件名可以通过以下SQL查询获得:
select d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
from ( select p.spid from sys.v$mystat m,sys.v$session s,sys.v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
( select t.instance from sys.v$thread t,sys.v$parameter v where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
( select value from sys.v$parameter where name = 'user_dump_dest') d;
/
整理自互联网
