Oracle中会话跟踪或SQL跟踪方法总结

发表于 更新于 分类于 ， 阅读次数： 本文字数： 2.2k 阅读时长 ≈ 8 分钟
初始化参数方式，DBMS_MONITOR包方式，10046事件，oradebug，DBMS_SYSTEM包方式，DBMS_SESSION包方式

前言

从内部技术细节看，SQL跟踪是基于10046调试事件的，下面是支持的等级：==所谓sql跟踪，一般是在会话级别进行跟踪==。

在Oracle 9i或者之前，下面SQL语句针对所在会话激活SQL跟踪：

1
alter session set sql_trace = true

还可通过dbms_session包中的set_sql_trace存储过程，或者通过dbms_system包的set_sql_tarce_in_session存储过程方法，但这些都只是在等级1激活SQL跟踪，在实践中用处不大，就不详述了。
更有用的是指定级别的方式，下面是对所在会话开始等级12的SQL跟踪：

1
alter session set events '10046 trace name context forever, level 12'

对应的对所在会话禁止SQL跟踪的语句如下：

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

也可以通过dbms_system包中的set_ev存储过程，这里也不详述了，我下面重点讲讲Oracle 10g之后提供的方法。
Oracle 10g之后提供了dbms_monitor包来开启或关闭SQL跟踪，提供了在会话、客户端、组件以及数据库层级开启SQL跟踪方法，注意只有dba角色的用户才允许使用。

注：sql_trace和10046设置代码跟踪==只能针对本会话或者系统级==进行会话跟踪，具体设置某个非本会话的跟踪需要采用oradebug或者
dbms_system.set_ev或者dbms_monitor.session_trace_enable。

方法1：传统参数方式

1
2
3
4
5
6
7
8
9
10
11
12
-- 全局系统级跟踪
alter system set sql_trace=true;
-- 每当发生严重错误时，服务器进程都会写入跟踪文件。 
-- 此外，设置初始化参数` SQL_TRACE = TRUE `会导致 SQL 跟踪工具为实例的所有 SQL 语句的处理生成性能统计信息，并将它们写入自动诊断存储库。

-- 跟踪当前会话
-- 注意：只能针对本会话或者系统级进行会话跟踪
-- 所谓的sql跟踪其实跟会话跟踪应该是一个东西。
1. 打开跟踪：`alter session set sql_trace=true`;
2. 为跟踪文件做标记：`alter session set tracefile_identifier='xx_trace';`
3. 停止跟踪：`alter session set sql_trace=false;`
4. 最后生成的跟踪文件可以采用`tkprof`工具生成来查看。

方法2：DBMS_MONITOR

DBMS_MONITOR有很多监控功能，详情参见dbms_monitor

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
SYS@orcl> ALTER SESSION SET tracefile_identifier = emp; -- 设置一个跟踪文件标识符

Session altered.

SYS@orcl> exec sys.dbms_monitor.session_trace_enable ( waits => true,binds => true);  -- 跟踪当前会话，详细使用参见dbms_monitor

PL/SQL procedure successfully completed.

SYS@orcl> select * from scott.emp;

     EMPNO ENAME                JOB                       MGR HIREDATE                   SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN                SALESMAN                 7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD                 SALESMAN                 7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES                MANAGER                  7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN               SALESMAN                 7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE                MANAGER                  7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK                MANAGER                  7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT                ANALYST                  7566 1987-04-19 00:00:00       3000                    20
      7839 KING                 PRESIDENT                     1981-11-17 00:00:00       5000                    10
      7844 TURNER               SALESMAN                 7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS                CLERK                    7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES                CLERK                    7698 1981-12-03 00:00:00        950                    30
      7902 FORD                 ANALYST                  7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER               CLERK                    7782 1982-01-23 00:00:00       1300                    10

14 rows selected.

SYS@orcl> exec sys.dbms_monitor.session_trace_disable ();

PL/SQL procedure successfully completed.

SYS@orcl> 

-- 找到跟踪文件
SYS@orcl> select value from v$diag_info where name = 'Default Trace File';

VALUE
-----------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_340336_EMP.trc

SYS@orcl> 
-- 提取跟踪文件名
select substr (
		value,
		instr ( value,'/',-1 ) + 1
		) TraceFileName
from v$diag_info
where name = 'Default Trace File';

-- 使用跟踪文件
下载跟踪文件，然后使用sql dev打开可以进行格式化查看，可以查看各种信息以及执行计划

方法3：10046事件

注意：只能针对本会话或者系统级进行会话跟踪.

10046事件级别：

  • 0
    禁止调试事件，停用SQL跟踪，等同于alter session set sql_trace = false

  • 1
    调试事件是激活的。针对每个被处理的数据库调用，给定如下信息：SQL语句、响应时间、服务时间
    处理的行数、处理的行数、逻辑读数量、物理读与写的数量、执行计划以及一些额外信息

    标准SQL跟踪，相当于alter session set sql_trace = true

  • 4
    在等级1的基础上，包括绑定变量的额外信息。主要是数据类型、精度以及每次执行时所用的值

  • 8
    在等级1的基础上，加上关于等待时间的细节信息。为了处理过程中的每个等待，提供如下信息：等待时间的名字、持续时间，以及一些额外的参数，可标明所等待的资源

  • 12
    同时启动等级4和等级8，在level 1的基础上增加绑定变量和等待事件的信息

全局设置：

1
2
3
4
5
6
-- 参数文件中指定: 
event="10046 trace name context forever,level 12"
-- 或者
SQL> alter system set events '10046 trace name context forever, level 12';
SQL> alter system set events '10046 trace name context off';
-- 注意：系统级别启用sql_trace，会产生大量trace文件，很容易耗尽磁盘空间，因此一般设置会话级别，并且及时关闭。

跟踪当前会话：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 必须具有alter session权限
GRANT ALTER SESSION TO user1;

-- 对当前会话启用level12跟踪
alter session set events '10046 trace name context forever,level 12';

exec sql

-- 关闭跟踪
alter session set events '10046 trace name context off';

-- 跟踪文件
select value from v$diag_info where name = 'Default Trace File'

方法4：oradebug

用如下语句找出要跟踪的spid

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 查询当前的sessionid
select * from v$session a where audsid = userenv('sessionid'); 
select s.USERNAME,
       s.OSUSER,
       s.SID,
       s.PADDR,
       s.PROCESS,
       p.spid     os_process_id,
       p.pid      oracle_process_id
  from v$session s, v$process p
 where s.paddr = p.addr
   and s.username = upper('LTWEBGIS')
   and s.SID = 145;

-- 设置跟踪进程id。
SQL> connect / as sysdba
SQL> oradebug setospid 5672; -- 该语句为跟踪其他会话（5672为v$process的spid），
-- 若跟踪本会话，执行 oradebug setmypid。
-- 或
SQL> connect / as sysdba
SQL> oradebug setorapid 15 -- 输入的15为v$process的pid

-- 设置跟踪文件大小无限制
SQL> oradebug unlimit;
-- 设置跟踪，级别8
SQL> oradebug event 10046 trace name context forever,level 8;
已处理的语句

-- 关闭跟踪
SQL> oradebug event 10046 trace name context off;

执行该语句（SQL> oradebug setospid 5672;）时，提示“ORA-01031: 权限不足”，
原因：oradebug是sysdba的命令（一般用户执行提示权限不足），而且是sqlplus特有的命令,不能在plsql工具中执行（否则提示无效sql）。
可以用oradebug help命令查看oradebug工具说明。

方法5：dbms_system

必须用sys用户执行，常用于跟踪其他会话

dbms_system.set_v设置10046事件

1
2
3
4
5
6
7
8
9
10
SQL> select sid,serial#,username from v$session where ...;
SQL> exec dbms_system.set_ev(sid,serial#,10046,12,'');   -- 启用 10046 lv12
SQL> exec dbms_system.set_ev(sid,serial#,10046,0,'');    -- 停止跟踪

-- 示例：
exec dbms_system.set_ev(si => 159,se => 254,ev => 10046,le =>8 ,nm => ''); -- 会话id等参数必须设置正确，否则trace无法生成
 -- 生成后可用sql语句查看trace文件位置。

-- 停止跟踪       
 exec dbms_system.set_ev(si => 159,se => 254,ev => 10046,le =>0 ,nm => '');

dbms_system.set_sql_trace_in_session

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 1.查询需要跟踪的会话的sid,serial#
SQL> select username,serial#,sid from v$session where username='SYS';

USERNAME                          SERIAL#           SID
------------------------------ ----------    ----------
SYS                                          25         	17

-- 2.针对指定会话启用跟踪
exce sys.dbms_system.set_sql_trace_in_session(17,25,true);      #(sid,serial)  等价于10046 lv1

trace文件位置：
select tracefile from v$process where addr  in (select paddr from v$session where sid in (select sid from v$mystat)；

-- 3.关闭指定会话的跟踪
exce sys.dbms_system.set_sql_trace_in_session(sid,serial,false);

-- 4.使用tkprof对跟踪文件进行格式化
tkprof aaa.trc aaa.txt

-- 5.查看aaa.txt文件

方法6：dbms_session

==只能跟踪当前会话==，不能跟踪指定会话。

dbms_session.set_sql_trace跟踪当前会话:

1
2
3
4
5
-- 
SQL> exec dbms_session.set_sql_trace(true);
SQL> 执行sql
SQL> exec dbms_session.set_sql_trace(false);
-- 等价于alter session set sql_trace

dbms_session.session_trace_enable:

1
2
3
4
-- 该过程不仅可看到等待事件信息还可看到绑定变量信息，相当于lv12
SQL> exec dbms_session.session_trace_enable(waits=>true,binds=>true);
SQL> 执行sql
SQL> exec dbms_session.session_trace_enable();

获取跟踪文件：

oradebug获取跟踪文件

1
2
3
4
5
6
7
8
9
-- 使用oradebug获取本会话跟踪文件位置
SQL> oradebug setmypid
SQL> oradebug tracefile_name

-- 获取任意会话跟踪文件位置
SQL> oradebug setospid 5392
已处理的语句
SQL> oradebug tracefile_name
d:\oracle\product\10.2.0\admin\fgisdb\udump\fgisdb_ora_5600.trc

sql获取跟踪文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- sql查看当前session跟踪文件位置
 SELECT d.value || '\' || lower(rtrim(i.instance_name, 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 instance_name FROM v$instance) i,
        (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;

-- 以下sql为查询输入的spid的会话跟踪文件
 select d.value || '\' || lower(rtrim(i.instance_name, chr(0))) || '_ora_' ||
       &spid || '.trc' trace_file_name
  from (SELECT instance_name FROM v$instance) i,
       (select value from v$parameter where name = 'user_dump_dest') d;

通过v$diag_info获取

1
2
3
4
5
SYS@orcl> select value from v$diag_info where name = 'Default Trace File';

VALUE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14323.trc

查看跟踪级别

1
2
3
4
5
6
7
8
9
10
11
12
-- 1.查看当前session的跟踪级别（必须在sys用户下执行）
declare
  event_level number;
begin
  dbms_system.read_ev(10046,event_level);
  dbms_output.put_line(to_char(event_level));
end;

-- 2.执行如下语句查看跟踪事件的跟踪级别
SQL> oradebug setospid spid --先指定要查看跟踪级别的spid
SQL> oradebug eventdump session
10046 trace name CONTEXT level 8, forever