SQL_TRACE及10046事件

一、SQL_TRACE

在使用 SQL_TRACE 之前，几个注意事项需要简单说明一下：

1． 初始化参数 TIMED_STATISTICS

参数 TIMED_STATISTICS 最好设置为 True，否则一些重要信息不会被收集。

2． 设置 MAX_DUMP_FILE_SIZE

该参数设置跟踪文件的大小限制，可以以操作系统块为单位设置；也可以以 K|M 为
单位设置；如果跟踪的信息较多，可以干脆设置为 UNLIMITED

在 Session 级可以设置如下：
SQL> alter session set MAX_DUMP_FILE_SIZE=unlimited;

注意：你需要足够的空间保存 trace 文件，跟踪过程产生的 Trace 文件可能远
远大于你的想象。
SQL_TRACE 可以作为初始化参数在全局启用，也可以通过命令行方式在具体 session 启
用。

1． 在全局启用 SQL_TRACE

在参数文件(pfile/spfile)中指定:
sql_trace = true
在全局启用 SQL_TRACE 会导致所有进程的活动被跟踪，包括后台进程及所有用户进程，
这通常会导致比较严重的性能问题，所以在生产环境中要谨慎使用。

2． 在当前 session 级设置

在 session 级启用和停止 sql_trace 方式如下:
SQL> alter session set sql_trace=true;
结束跟踪:
SQL> alter session set sql_trace=false;

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 from v$session where username is not null;
 SID        SERIAL#    USERNAME
 ---------- ---------- ------------------------------
 8          2041       SYS
 9          437        EYGLE

设置跟踪:
SQL> exec dbms_system.set_sql_trace_in_session(9,437,true)

停止跟踪:
SQL> exec dbms_system.set_sql_trace_in_session(9,437,false)

如果要对其他用户的参数进行设置，我们可能需要用到 DBMS_SYSTEM 包中的另外一个过程：

比如设置 MAX_DUMP_FILE_SIZE 等参数，可以参考如下：
SQL> select sid,serial#,username from v$session where username is not null;
 
 SID       SERIAL# USERNAME
 ---------- ---------- ------------------------------
 18 1605 EYGLE

SQL> begin
 2 sys.dbms_system.set_bool_param_in_session(18, 1605, 'timed_statistics', true);
 3 sys.dbms_system.set_int_param_in_session(18, 1605, 'max_dump_file_size', 2147483647);
 4 sys.dbms_system.set_sql_trace_in_session(18, 1605, true);
 5 end;
 6 /
 
二、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 级设置。

1． 在全局设置

在参数文件中增加:
event="10046 trace name context forever,level 12"
此设置对所有用户的所有进程生效、包括后台进程.

2． 对当前 session 设置

通过 alter session 的方式修改，需要 alter session 的系统权限:
SQL> alter session set events '10046 trace name context forever, level 8';
Session altered.
SQL> alter session set events '10046 trace name context off';
Session altered.

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 信息:
SQL> select sid,serial#,username from v$session where username is not null;
 SID SERIAL#    USERNAME
 ---------- ---------- ------------------------------
 8 2041       SYS
 9 437        EYGLE

执行跟踪:
SQL> exec dbms_system.set_ev(9,437,10046,8,'eygle');

结束跟踪:
SQL> exec dbms_system.set_ev(9,437,10046,0,'eygle');

三、获取跟踪文件

生成的跟踪文件位于 user_dump_dest 目录中，位置及文件名可以通过以下 SQL 查询获得:

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
 /
 
 TRACE_FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/udump/prod_ora_2408.trc

读取当前 session 设置的参数

当我们通过 alter session 的方式设置了 sql_trace,这个设置是不能通过 show parameter 的方
式得到的,我们需要通过 dbms_system.read_ev 来获取

SQL> set feedback off
SQL> set serveroutput on
SQL> declare
 event_level number;
 begin
 for event_number in 10000..10999 loop
 sys.dbms_system.read_ev(event_number, event_level);
 if (event_level > 0) then
 sys.dbms_output.put_line(
 'Event ' ||
 to_char(event_number) ||
 ' is set at level ' ||
 to_char(event_level)
 );
 end if;
end loop;
end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27064835/viewspace-1623824/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27064835/viewspace-1623824/