如今，一般DBA使用会话跟踪、SQL跟踪并进行分析已不是稀奇的事情，我估计常用的方法有SQL_TRACE、10046事件等。另外，如果我们需要跟踪其他会话，那需要如何做呢？大家估计最熟悉的是使用ORADEBUG，至少我喜欢这样做。其实，Oracle提供的跟踪方法非常丰富，本文就将这些方法一一介绍，大家可以选择自己喜欢的方式并熟练使用它们。

 

TRACE目录

首先，我可能会关心咱跟踪的trace文件放在哪里，Oracle 10g和11g存放trace目录是不一样的，Oracle 10g中一般是$ORACLE_BASE/admin/$ORACLE_SID/udump和$ORACLE_BASE/admin/$ORACLE_SID/bdump(往往关心udump)，而11g则引入了ADR(Automatic Diagnostic Repository)新特性，trace文件就放于{adr_base}/diag/rdbms/{database_name}/$ORACLE_SID/trace下。关于Oracle 11g的ADR相关内容请阅读官方NOTE或者我写的一篇文章《L谈Automatic Diagnostic Repository(ADR)》

 

 

Oracle 11g Trace目录中{adr_base}如下查看：

luocs@MAA> show parameter diag

NAME                                 TYPE                   VALUE ------------------------------------ ---------------------- ------------------------------ diagnostic_dest string /u01/app/oracle

 

跟踪文件的前台进程会被定向到USER_DUMP_DEST参数所指定的目录，而跟踪文件的后台进程则使用BACKGROUND_DUMP_DEST参数所指向的目录。无论在哪种情况下，TRACE的后缀都是.trc。

 

Oracle 10g中，我们可以如下查看trace目录

sys@LTB> show parameter user_dump_dest

NAME                                 TYPE                   VALUE ------------------------------------ ---------------------- ------------------------------ user_dump_dest string /u01/app/oracle/admin/ltb/udump
sys@LTB> show parameter background_dump_dest

NAME                                 TYPE                   VALUE ------------------------------------ ---------------------- ------------------------------ background_dump_dest string /u01/app/oracle/admin/ltb/bdump 或者通过查v$parameter获得 sys@LTB> select name, value from v$parameter where name in ('user_dump_dest','background_dump_dest'); NAME                                VALUE ----------------------------------- ----------------------------------------------------------------- background_dump_dest /u01/app/oracle/admin/ltb/bdump
user_dump_dest /u01/app/oracle/admin/ltb/udump

 

而Oracle 11g中，前台进程和后台进程所指定的trace目录是相同的

sys@MAA> select name, value from v$parameter where name in ('user_dump_dest','background_dump_dest'); NAME                 VALUE -------------------- ----------------------------------------------------------------- background_dump_dest /u01/app/oracle/diag/rdbms/maa/maa/trace
user_dump_dest /u01/app/oracle/diag/rdbms/maa/maa/trace

 

 

另外，如果查找当前会话的trace文件，也可以使用如下脚本：

sys@LTB> column trace new_val T
sys@LTB> select c.value || '/' || d.instance_name || '_ora_' || 2 a.spid || '.trc' || 3 case when e.value is not null then '_'||e.value end trace 4 from v$process a, v$session b, v$parameter c, v$instance d, v$parameter e 5 where a.addr = b.paddr 6 and b.audsid = userenv('sessionid') 7 and c.name = 'user_dump_dest' 8 and e.name = 'tracefile_identifier' 9 / TRACE ------------------------------------------------------------------ /u01/app/oracle/admin/ltb/udump/ltb_ora_3471.trc

 

但如果你是普通用户，无法show parameter查看，那么可以通过如下方式获得trace目录：

luocs@LTB> set serveroutput on size 1000000 for wra
luocs@LTB> declare 2 paramname varchar2(256); 3 integerval binary_integer; 4 stringval varchar2(256); 5 paramtype binary_integer; 6 begin 7 paramtype:=dbms_utility.get_parameter_value('user_dump_dest',integerval,stringval); 8 if paramtype=1 then 9 dbms_output.put_line(stringval); 10 else 11 dbms_output.put_line(integerval); 12 end if; 13 end; 14 /
/u01/app/oracle/admin/ltb/udump

PL/SQL procedure successfully completed.

 

这方法在11g中也适用，但11g的ADR特性让你更方便的获得trace目录，查询v$diag_info即可，如下：

luocs@MAA> select value from v$diag_info where name = 'Default Trace File'; VALUE ---------------------------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_15852.trc

 

Oracle为安全考虑，trace文件只能被oracle用户或者oinstall用户组成员才能读取，其他用户试图读取该文件会收到错误报告，如下：

[root@primary ~]# ls -l /u01/app/oracle/admin/ltb/udump/ total 40 -rw-r----- 1 oracle oinstall 638 Jan 21 20:12 ltb_ora_27185.trc -rw-r----- 1 oracle oinstall 954 Jan 21 20:13 ltb_ora_27217.trc [luocs@primary ~]$ id
uid=501(luocs) gid=502(luocs) groups=502(luocs) [luocs@primary ~]$ cat /u01/app/oracle/admin/ltb/udump/ltb_ora_27185.trc
cat: /u01/app/oracle/admin/ltb/udump/ltb_ora_27185.trc: Permission denied

 

其实我们也可以让普通用户阅读trace内容，Oracle是通过一个隐含参数来控制这些限制的，即_trace_files_public，此隐含参数默认值为FALSE，即不允许普通用户阅读trace文件：

sys@MAA> set pagesize 9999 sys@MAA> set line 130 sys@MAA> col NAME for a20
sys@MAA> col VALUE for a20
sys@MAA> col DESCRIB for a80
sys@MAA> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIB 2 FROM SYS.x$ksppi x, SYS.x$ksppcv y 3 WHERE x.inst_id = USERENV ('Instance') 4 AND y.inst_id = USERENV ('Instance') 5 AND x.indx = y.indx 6 AND x.ksppinm LIKE '%&par%' 7 / Enter value for par: _trace_files_public
old 6: AND x.ksppinm LIKE '%&par%' new 6: AND x.ksppinm LIKE '%_trace_files_public%' NAME                 VALUE                DESCRIB -------------------- -------------------- -------------------------------------------------------------------------------- _trace_files_public  FALSE Create publicly accessible trace files

 

我们可以修改为TRUE来破坏这限制，如下：

sys@LTB> alter system set "_trace_files_public"=TRUE scope=spfile; System altered. sys@LTB> startup force
ORACLE instance started. Total System Global Area 520093696 bytes Fixed Size 2021984 bytes Variable Size 150996384 bytes Database Buffers 360710144 bytes Redo Buffers 6365184 bytes Database mounted. Database opened. sys@LTB> oradebug setmypid Statement processed. sys@LTB> oradebug tracefile_name /u01/app/oracle/admin/ltb/udump/ltb_ora_3608.trc

sys@LTB> ! ls -l /u01/app/oracle/admin/ltb/udump/ltb_ora_3608.trc -rw-r--r-- 1 oracle oinstall 1752 Jan 21 20:39 /u01/app/oracle/admin/ltb/udump/ltb_ora_3608.trc

– 可见普通用户已经有读取权限了(但，你真正去阅读的时候还会受目录的限制，也需要相应设置，不建议这么做)

 

跟踪级别(TRACE LEVEL)

玩转跟踪不了解跟踪级别是不行的，下面介绍下Oracle可采用的跟踪接口：

Level 0 = No statistics generated

Level 1 = standard trace output including parsing, executes and fetches plus more.

Level 2 = Same as level 1.

Level 4 = Same as level 1 but includes bind information

Level 8 = Same as level 1 but includes wait's information

Level 12 = Same as level 1 but includes binds and waits

 

不光是10046 事件，其他事件相关的level信息我们可阅读$ORACLE_HOME/rdbms/mesg/oraus.msg文件。

 

跟踪当前会话

1、sql_trace

luocs@MAA> alter session set sql_trace=true;

luocs@MAA> — execute our code

luocs@MAA> alter session set sql_trace=false;

 

2、10046 events

luocs@MAA> alter session set events '10046 trace name context forever,level 1';

luocs@MAA> — execute our code

luocs@MAA> alter session set events '10046 trace name context off';

 

另外，我们还可以使用dbms_system、dbms_support、dbms_monitor、oradebug方式进行跟踪，这些都在下面介绍。

 

跟踪其他会话

有的时候，我们需要用DBA权限用户去跟踪某个用户的SESSION，这时候，我们先要获取SID、SERIAL#或者进程号，方法如下：

-- 获取当前会话的SID,SERIAL# luocs@MAA> select sid, serial# from v$session where sid = (select sid from v$mystat where rownum=1); SID    SERIAL# ---------- ---------- 41 1147 -- 获取当前会话的PID和SPID
luocs@MAA> select pid, spid from v$process p, v$session s where p.addr = s.paddr and s.sid=(select sid from v$mystat where rownum=1); PID SPID ---------- ------------------------------------------------ 24 16434 -- 获取系统级别的SID与SERIAL#等 sys@MAA> select s.sid,s.serial#,s.username,s.osuser 2 from v$session s,v$process p 3 where s.paddr=p.addr; SID    SERIAL# USERNAME   OSUSER ---------- ---------- ---------- ---------- 2 1 oracle 3 1 oracle 4 1 oracle 5 1 oracle 6 1 oracle 7 1 oracle 8 1 oracle 9 1 oracle 10 1 oracle 11 1 oracle 12 1 oracle 13 1 oracle 14 1 oracle 15 1 oracle 16 1 oracle 17 1 oracle 18 1 oracle 20 1 oracle 23 23 oracle 28 271 oracle 41 1149 LUOCS      oracle 53 21805 LUOCS      oracle 25 7 oracle 27 1 oracle 40 1127 SYS        oracle 29 5 oracle 30 1 oracle 34 1 oracle 22 11 oracle 44 21617 SYS        oracle 47 833 XLZHGJ     oracle 38 3 oracle 37 13 oracle 21 79 oracle 48 1303 oracle 35 rows selected.

 

下面介绍几个跟踪会话方法

1、DBMS_SYSTEM

DBMS_SYSTEM是Oracle 10g之前常用的跟踪作用的包，虽然我们在Oracle 10g之后的版本无法看到这个包的相关说明，但功能还是能用的。

– 设置时间相关统计收集

luocs@MAA> exec dbms_system.set_bool_param_in_session(41,1149,'timed_statistics',true);

 

– 设置max_dump_file_size以trace文件大小足够容纳信息

luocs@MAA> exec dbms_system.set_int_param_in_session(41,1149,'max_dump_file_size',20000000);

 

– 设置10046事件并且指定LEVEL

luocs@MAA> exec dbms_system.set_ev(41,1149,10046,12,'');

 

– 激活trace跟踪

luocs@MAA> exec dbms_system.set_sql_trace_in_session(41,1149,true);

 

– 停止trace跟踪

luocs@MAA> exec dbms_system.set_sql_trace_in_session(41,1149,false);

luocs@MAA> exec dbms_system.set_ev(41,1149,10046,0,'');

 

下面是我的演示内容：

luocs@MAA> select sid, serial# from v$session where sid = (select sid from v$mystat where rownum=1); SID    SERIAL# ---------- ---------- 53 21805 luocs@MAA> exec dbms_system.set_bool_param_in_session(53,21805,'timed_statistics',true); PL/SQL procedure successfully completed. luocs@MAA> exec dbms_system.set_int_param_in_session(53,21805,'max_dump_file_size',20000000); PL/SQL procedure successfully completed. luocs@MAA> exec dbms_system.set_ev(53,21805,10046,12,''); PL/SQL procedure successfully completed. luocs@MAA> exec dbms_system.set_sql_trace_in_session(53,21805,true); PL/SQL procedure successfully completed. luocs@MAA> variable x number
luocs@MAA> exec :l := 1 PL/SQL procedure successfully completed. luocs@MAA> select count(*) from test where id=:l; COUNT(*) ---------- 1 luocs@MAA> exec :l := 100 PL/SQL procedure successfully completed. luocs@MAA> select count(*) from test where id=:l; COUNT(*) ---------- 9999 luocs@MAA> exec dbms_system.set_sql_trace_in_session(53,21805,false); PL/SQL procedure successfully completed. luocs@MAA> exec dbms_system.set_ev(53,21805,10046,0,''); PL/SQL procedure successfully completed. luocs@MAA> select value from v$diag_info where name = 'Default Trace File'; VALUE ---------------------------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_15826.trc

trace部分内容： ===================== PARSING IN CURSOR #47603153651720 len=21 dep=0 uid=51 oct=47 lid=51 tim=1359462720172941 hv=3459344829 ad='9ed28e20' sqlid='0haapcz732udx' BEGIN :l := 1; END; END OF STMT
PARSE #47603153651720:c=0,e=670,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1359462720172939 EXEC #47603153651720:c=2000,e=1491,p=0,cr=0,cu=0,mis=1,r=1,dep=0,og=1,plh=0,tim=1359462720174513 PARSE #47603153650544:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1359462720174801 EXEC #47603153650544:c=0,e=186,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1359462720175049 *** 2013-01-29 20:32:11.351 CLOSE #47603153651720:c=0,e=31,dep=0,type=0,tim=1359462731351173 CLOSE #47603153650544:c=0,e=38,dep=0,type=3,tim=1359462731351294 ===================== PARSING IN CURSOR #47603153651720 len=37 dep=0 uid=51 oct=3 lid=51 tim=1359462731351922 hv=874746037 ad='9ed283d0' sqlid='9pukpvhu2745p' select count(*) from test where id=:l END OF STMT
PARSE #47603153651720:c=0,e=546,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1359462731351921 EXEC #47603153651720:c=2000,e=2221,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=4002023942,tim=1359462731354222 FETCH #47603153651720:c=0,e=170,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=4002023942,tim=1359462731354557 STAT #47603153651720 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=2 pr=0 pw=0 time=174 us)' STAT #47603153651720 id=2 cnt=1 pid=1 pos=1 obj=25916 op='INDEX RANGE SCAN INX_TEST_ID (cr=2 pr=0 pw=0 time=146 us cost=1 size=3 card=1)' FETCH #47603153651720:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=4002023942,tim=1359462731354893 PARSE #47603153650544:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1359462731355442 EXEC #47603153650544:c=0,e=206,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1359462731355711 *** 2013-01-29 20:32:17.365 CLOSE #47603153651720:c=0,e=13,dep=0,type=0,tim=1359462737365691 CLOSE #47603153650544:c=0,e=25,dep=0,type=3,tim=1359462737365809

 

2、DBMS_SUPPORT

早期版本提供的跟踪相关包还有DBMS_SUPPORT，它可以跟踪用户的SESSION信息。DBMS_SUPPORT默认没有安装，我们需要手动执行脚本来安装

luocs@MAA> desc dbms_support

ERROR:

ORA-04043: object dbms_support does not exist

 

– INSTALL THE PACKAGE

sys@MAA> @?/rdbms/admin/dbmssupp

 

Package created.

 

 

Package body created.

 

sys@MAA> @?/rdbms/admin/prvtsupp.plb

 

Package body created.

 

– 查看版本

sys@MAA> select dbms_support.PACKAGE_VERSION from dual;

 

PACKAGE_VERSION

———————————————————————————————————————————-

DBMS_SUPPORT Version 1.0 (17-Aug-1998) – Requires Oracle 7.2 – 8.0.5

 

– 开启某session的跟踪，可以选择等待信息和绑定变量信息

 

sys@MAA> exec dbms_support.start_trace_in_session(53,21805,waits=>false,binds=>true);

 

– 停止某session的跟踪

sys@MAA> exec dbms_support.stop_trace_in_session(53,21805)

 

– 启动跟踪当前session

sys@MAA> exec dbms_support.start_trace(waits=>false,binds=>true);

 

– 停止跟踪当前session

sys@MAA> exec dbms_support.stop_trace;

 

下面是我的演示：

luocs@MAA> select sid, serial# from v$session where sid = (select sid from v$mystat where rownum=1); SID    SERIAL# ---------- ---------- 53 21807 sys@MAA> exec dbms_support.start_trace_in_session(53,21807,waits=>false,binds=>true); PL/SQL procedure successfully completed. luocs@MAA> variable x number
luocs@MAA> exec :l := 100 PL/SQL procedure successfully completed. luocs@MAA> select count(*) from test where id=:l 2 ; COUNT(*) ---------- 9999 sys@MAA> exec dbms_support.stop_trace_in_session(53,21807); PL/SQL procedure successfully completed. luocs@MAA> select value from v$diag_info where name = 'Default Trace File'; VALUE ---------------------------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_16974.trc -- 部分TRACE内容 ===================== PARSING IN CURSOR #47953679205472 len=38 dep=0 uid=51 oct=3 lid=51 tim=1359464472023679 hv=3115683868 ad='9e731000' sqlid='fsa2yt2wvb40w' select count(*) from test where id=:l END OF STMT
PARSE #47953679205472:c=999,e=984,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1359464472023678 BINDS #47953679205472: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0 kxsbbbfp=2b9d16045340 bln=22 avl=02 flg=05 value=100 EXEC #47953679205472:c=2999,e=2765,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3727996439,tim=1359464472026526 FETCH #47953679205472:c=3000,e=2883,p=0,cr=62,cu=0,mis=0,r=1,dep=0,og=1,plh=3727996439,tim=1359464472029485 STAT #47953679205472 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=62 pr=0 pw=0 time=2884 us)' STAT #47953679205472 id=2 cnt=9999 pid=1 pos=1 obj=25916 op='INDEX FAST FULL SCAN INX_TEST_ID (cr=62 pr=0 pw=0 time=2190 us cost=11 size=29997 card=9999)' FETCH #47953679205472:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3727996439,tim=1359464472029832 PARSE #47953679348848:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1359464472030399 BINDS #47953679348848: Bind#0 oacdty=123 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=1000000 frm=00 csi=00 siz=4000 off=0 toid ptr value=A1E4A4A0 length=16 C7D9741553643AE0E0430100007F2FB7
  kxsbbbfp=2b9d1605df70 bln=4000 avl=00 flg=15 Bind#1 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=01 fl2=1000000 frm=00 csi=00 siz=24 off=0 kxsbbbfp=2b9d1605f310 bln=22 avl=22 flg=05 value=### An invalid number has been seen.Memory contents are : Dump of memory from 0x00002B9D1605F310 to 0x00002B9D1605F326 2B9D1605F310 000010C1 00000000 00000000 00000000 [................] 2B9D1605F320 00000000 00000000 [........] EXEC #47953679348848:c=0,e=393,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1359464472030861 -- 跟踪当前会话 sys@MAA> grant execute on dbms_support to luocs; Grant succeeded. luocs@MAA> SELECT DBMS_SUPPORT.MYSID from DUAL; SELECT DBMS_SUPPORT.MYSID from DUAL * ERROR at line 1: ORA-00904: "DBMS_SUPPORT"."MYSID": invalid identifier -- 这时候发现普通用户无法执行，我们需要如下授权 sys@MAA> grant execute on dbms_support to luocs; Grant succeeded. sys@MAA> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support; Synonym created. luocs@MAA> SELECT DBMS_SUPPORT.MYSID from DUAL; MYSID ---------- 53 luocs@MAA> exec dbms_support.start_trace(waits=>true,binds=>false); PL/SQL procedure successfully completed. luocs@MAA> select count(*) from test; COUNT(*) ---------- 10000 luocs@MAA> exec dbms_support.stop_trace; PL/SQL procedure successfully completed. -- trace部分内容 ===================== PARSING IN CURSOR #47179837486632 len=25 dep=0 uid=51 oct=3 lid=51 tim=1359465350267384 hv=297253644 ad='9ed2c088' sqlid='7b2twsn8vgfsc' select count(*) from test END OF STMT
PARSE #47179837486632:c=0,e=144,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1950795681,tim=1359465350267383 EXEC #47179837486632:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1950795681,tim=1359465350267512 WAIT #47179837486632: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1359465350267576 FETCH #47179837486632:c=2000,e=1851,p=0,cr=37,cu=0,mis=0,r=1,dep=0,og=1,plh=1950795681,tim=1359465350269457 STAT #47179837486632 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=37 pr=0 pw=0 time=1832 us)' STAT #47179837486632 id=2 cnt=10000 pid=1 pos=1 obj=25886 op='TABLE ACCESS FULL TEST (cr=37 pr=0 pw=0 time=6947 us cost=12 size=0 card=10000)' WAIT #47179837486632: nam='SQL*Net message from client' ela= 164 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1359465350269784 FETCH #47179837486632:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1950795681,tim=1359465350269824 WAIT #47179837486632: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1359465350269849 WAIT #47179837486632: nam='SQL*Net message from client' ela= 458 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1359465350270323 PARSE #47179837532376:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1359465350270400 WAIT #47179837532376: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1359465350270667 EXEC #47179837532376:c=0,e=239,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1359465350270709

 

3、DBMS_MONITOR

DBMS_MONITOR是Oracle 从10g开始引入的程序包，它即可以实现传统的跟踪功能，也可以在拥有连接池或共享服务器等的多层平台中使用，请阅读下段文：

Oracle 10g offers a new package to allow sessions to be traced end to end in multi-tier architectures that share sessions using connection pooling or multi-threading. This package allows applications written using for instance JDBC / Java or something like Forte to be traced where it would normally be very difficult to identify a database session belonging to a client as the sessions / clients pairings change with time.

 

The new functionality works in three levels. You can use the old SID / SERIAL# pairings to identify a session but you can also use a client identifier or a service name / module / action to identify a client session to be traced. The package also offers a set of procedures to allow statistics to be gathered for the same groups. These statistics can then be selected from dynamic views.

 

下面就看一下我的演示：

-- 使用传统方式，通过SID和SERIAL#来跟踪会话 luocs@MAA> select sid, serial# from v$session where sid = (select sid from v$mystat where rownum=1); SID    SERIAL# ---------- ---------- 53 21813 -- 启动跟踪 sys@MAA> exec DBMS_MONITOR.SESSION_TRACE_ENABLE(53,21813,true,true); PL/SQL procedure successfully completed. luocs@MAA> variable x number
luocs@MAA> exec :l := 1; PL/SQL procedure successfully completed. luocs@MAA> select count(*) from test where id=:l; COUNT(*) ---------- 1 -- 停止跟踪 sys@MAA> exec DBMS_MONITOR.SESSION_TRACE_DISABLE(53,21813); PL/SQL procedure successfully completed. TRACE内容略。

 

下面是SESSION_TRACE_ENABLE的参数

 

– 当前会话跟踪

luocs@MAA> EXEC DBMS_MONITOR.session_trace_enable;

luocs@MAA> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);

luocs@MAA> EXEC DBMS_MONITOR.session_trace_disable;

 

– 另外，我们也可以使用客户端标识符来进行跟踪

下面是我一个使用例子 luocs@WWW> BEGIN 2 DBMS_SESSION.SET_IDENTIFIER(''); 3 DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE 4 (CLIENT_ID => '', 5 WAITS => TRUE 6 ); 7 END; 8 / PL/SQL procedure successfully completed. luocs@WWW> select /*+ parallel(3) */ count(*) from test1; COUNT(*) ---------- 456128 luocs@WWW> exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(''); PL/SQL procedure successfully completed. sys@WWW> select client_identifier from v$session where sid=(select sid from v$mystat where rownum=1); CLIENT_IDENTIFIER ---------------------------------------------------------------------------------------------------- www.luocs.com

sys@WWW> col PRIMARY_ID for a30
sys@WWW> select trace_type, primary_id, waits, binds from dba_enabled_traces; TRACE_TYPE                                 PRIMARY_ID                     WAITS      BINDS ------------------------------------------ ------------------------------ ---------- ---------- CLIENT_ID                                  www.luocs.com                  TRUE       FALSE [oracle@rac1 ~]$ cd /u01/app/oracle/diag/rdbms/www/ltb1/trace/ [oracle@rac1 trace]$ trcsess clientid=www.luocs.com output=luocs_test2.trc [oracle@rac1 trace]$ ls luocs_test2.trc -rw-r--r-- 1 oracle oinstall 103514 Jan 27 07:06 luocs_test2.trc -- trace内容略

 

关于DBMS_MONITOR更多更详尽的内容，请阅读官方NOTE PL/SQL Packages and Types Reference部分。

 

4、 DBMS_SESSION

DBMS_SESSION也有几个PL/SQL是和跟踪相关的，它们就是SESSION_TRACE_ENABLE/DISABLE、SET_SQL_TRACE，注意DBMS_SESSION只能跟踪当前会话，无法跟踪其他SESSION。

 

好，理论介绍还是减少点，请阅读官方NOTE。

 

下面是我的演示：

-- SESSION_TRACE_ENABLE/DISABLE过程可以设置等待和变量跟踪 luocs@MAA> exec DBMS_SESSION.SESSION_TRACE_ENABLE(TRUE,TRUE); luocs@MAA> exec DBMS_SESSION.SESSION_TRACE_ENABLE(WAITS=>TRUE, BINDS=>TRUE); luocs@MAA> variable x number
luocs@MAA> exec :l := 100 PL/SQL procedure successfully completed. luocs@MAA> select count(*) from test where id=:l; COUNT(*) ---------- 9999 -- 停止跟踪 luocs@MAA> exec DBMS_SESSION.SESSION_TRACE_DISABLE(); -- trace内容略 -- SET_SQL_TRACE过程好比alter session set sql_trace=true|false luocs@MAA> exec DBMS_SESSION.SET_SQL_TRACE(TRUE); PL/SQL procedure successfully completed. luocs@MAA> variable x number
luocs@MAA> exec :l := 100 PL/SQL procedure successfully completed. luocs@MAA> select count(*) from test where id=:l; COUNT(*) ---------- 9999 luocs@MAA> exec DBMS_SESSION.SET_SQL_TRACE(FALSE); PL/SQL procedure successfully completed. -- trace内容里看不到绑定变量和等待的信息

 

5、ORADEBUG

ORADEBUG功能非常强大，我们执行oradebug help将会看到非常多的功能可使用，我也非常喜欢使用它，甚至有的时候10046、10053等events都是通过ORADEBUG来设置。ORADEBUG是SYS的工具，即使权限较大的SYSTEM用户都无法使用它：

system@MAA> show user
USER is "SYSTEM" system@MAA> oradebug setmypid
ORA-01031: insufficient privileges

 

因此，oradebug只能跟踪SYS用户的当前SESSION，如下方式：

sys@MAA> oradebug setmypid
sys@MAA> oradebug unlimit
sys@MAA> oradebug event 10046 trace name context forever, level 12 sys@MAA> exec our code
sys@MAA> oradebug event 10046 trace name context off
sys@MAA> oradebug tracefile_name /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_17641.trc

 

当然，我们通过oradebug非常方便地跟踪其他会话，如下方式：

luocs@MAA> select pid, spid from v$process p, v$session s where p.addr = s.paddr and s.sid=(select sid from v$mystat where rownum=1); PID SPID ---------- ------------------------------------------------ 25 17678 -- 指定跟踪SESSION的SPID(OS process) sys@MAA> oradebug setospid 17678 Oracle pid: 25, Unix process pid: 17678, image: oracle@maa3.luocs.com (TNS V1-V3) -- 或者指定跟踪SESSION的PID(Oracle process ID) sys@MAA> oradebug setorapid 25 Oracle pid: 25, Unix process pid: 17678, image: oracle@maa3.luocs.com (TNS V1-V3) sys@MAA> oradebug unlimit
sys@MAA> oradebug event 10053 trace name context forever, level 1 sys@MAA> exec our code
sys@MAA> oradebug event 10053 trace name context off
sys@MAA> oradebug tracefile_name

 

以上五种方法，不要求DBA全会，在工作中选择自己喜欢的一两种方式，能够熟练使用即可。

 

另外，查看SQL TRACE还有DBA常用的AUTOTRACE(SQL*Plus)和10053 EVENTS(CBO trace)。

-- AUTOTRACE
luocs@MAA> set autotrace on
luocs@MAA> set autotrace on explain
luocs@MAA> set autotrace on statistics
luocs@MAA> set autotrace traceonly 
luocs@MAA> set autotrace traceonly explain
luocs@MAA> set autotrace traceonly explain statistics
luocs@MAA> set autotrace off 我喜欢使用缩略方式，比如 luocs@MAA> set autot trace exp stat -- 10053 EVENTS
luocs@MAA> alter session set events '10053 trace name context forever, level 1'; luocs@MAA> alter session set events '10053 trace name context off'; sys@MAA> oradebug event 10053 trace name context forever, level 1 sys@MAA> oradebug event 10053 trace name context off