如今，一般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 Trace目录中{adr_base}如下查看：
1 SQL> show parameter diag2 3 NAME TYPE VALUE4 -------------------------- ----------- ------------------------------5 diagnostic_dest string /u01/app/oracle
跟踪文件的前台进程会被定向到USER_DUMP_DEST参数所指定的目录，而跟踪文件的后台进程则使用BACKGROUND_DUMP_DEST参数所指向的目录。无论在哪种情况下，TRACE的后缀都是.trc。
Oracle 10g中，我们可以如下查看trace目录：
1 SQL> show parameter user_dump_dest 2 3 NAME TYPE VALUE 4 ------------------------------------ ---------------------- ------------------------------ 5 user_dump_dest string /u01/app/oracle/admin/yft/udump 6 SQL> show parameter background_dump_dest 7 8 NAME TYPE VALUE 9 ------------------------------------ ---------------------- ------------------------------10 background_dump_dest string /u01/app/oracle/admin/yft/bdump11 12 ----或者通过查v$parameter获得----13 SQL> select name, value from v$parameter where name in ('user_dump_dest','background_dump_dest');14 15 NAME VALUE16 ----------------------------------- -----------------------------------------------------------------17 background_dump_dest /u01/app/oracle/admin/yft/bdump18 user_dump_dest /u01/app/oracle/admin/yft/udump 而Oracle 11g中，前台进程和后台进程所指定的trace目录是相同的
1 SQL> select name,value from v$parameter where name in ('user_dump_dest','background_dump_dest');2 3 NAME VALUE4 ------------------------------ ---------------------------------------------5 background_dump_dest /u01/app/oracle/diag/rdbms/yft/yft/trace6 user_dump_dest /u01/app/oracle/diag/rdbms/yft/yft/trace 另外，如果查找当前会话的trace文件，也可以使用如下脚本：
1 SQL> column trace new_val T 2 SQL>select c.value || '/' || d.instance_name || '_ora_' || 3 a.spid || '.trc' || 4 case when e.value is not null then '_'||e.value end trace 5 from v$process a, v$session b, v$parameter c, v$instance d, v$parameter e 6 where a.addr = b.paddr 7 and b.audsid = userenv('sessionid') 8 and c.name = 'user_dump_dest' 9 and e.name = 'tracefile_identifier'10 /11 12 TRACE13 --------------------------------------------------------------------------------14 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_31139.trc 但如果你是普通用户，无法show parameter查看，那么可以通过如下方式获得trace目录：
1 SQL> set serveroutput on size 1000000 for wra 2 SQL> declare 3 2 paramname varchar2(256); 4 3 integerval binary_integer; 5 4 stringval varchar2(256); 6 5 paramtype binary_integer; 7 6 begin 8 7 paramtype:=dbms_utility.get_parameter_value('user_dump_dest',integerval,stringval); 9 8 if paramtype=1 then10 9 dbms_output.put_line(stringval);11 10 else12 11 dbms_output.put_line(integerval);13 12 end if;14 13 end;15 14 /16 /u01/app/oracle/diag/rdbms/yft/yft/trace17 18 PL/SQL procedure successfully completed. 这方法在11g中也适用，但11g的ADR特性让你更方便的获得trace目录，查询v$diag_info即可，如下：
1 SQL> select value from v$diag_info where name = 'Default Trace File';2 3 VALUE4 ------------------------------------------------------------5 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_31139.trc
Oracle为安全考虑，trace文件只能被oracle用户或者oinstall用户成员才能读取，其他用户试图读取该文件会收到错误报告，如下：
1 [oracle@yft ~]$ ll /u01/app/oracle/diag/rdbms/yft/yft/trace/ 2 total 440 3 -rw-r----- 1 oracle oinstall 34779 Feb 4 16:32 alert_yft.log 4 -rw-r----- 1 oracle oinstall 1065 Feb 4 16:18 yft_ckpt_30650.trc 5 -rw-r----- 1 oracle oinstall 59 Feb 4 16:18 yft_ckpt_30650.trm 6 -rw-r----- 1 oracle oinstall 920 Feb 4 16:22 yft_ckpt_30824.trc 7 。。。。。。 8 9 [jack@yft ~]$ id10 uid=501(jack) gid=502(jack) groups=502(jack)11 [jack@yft ~]$ cat /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_31139.trc12 cat: /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_31139.trc: Permission denied
其实我们也可以让普通用户阅读trace内容，Oracle是通过一个隐含参数来控制这些限制的，即_trace_files_public，此隐含参数默认设置值为FALSE，即不允许普通用户阅读trace文件：
1 SQL> set pagesize 9999 2 SQL> set line 130 3 SQL> col NAME for a20 4 SQL> col VALUE for a20 5 SQL> col DESCRIB for a80 6 SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIB 7 2 FROM SYS.x$ksppi x, SYS.x$ksppcv y 8 3 WHERE x.inst_id = USERENV ('Instance') 9 4 AND y.inst_id = USERENV ('Instance')10 5 AND x.indx = y.indx11 6 AND x.ksppinm LIKE '%&par%'12 7 /13 Enter value for par: _trace_files_public14 old 6: AND x.ksppinm LIKE '%&par%'15 new 6: AND x.ksppinm LIKE '%_trace_files_public%'16 17 NAME VALUE DESCRIB18 -------------------- -------------------- --------------------------------------------------------------------------------19 _trace_files_public FALSE Create publicly accessible trace files 我们可以修改为TRUE来破坏这限制，如下：
1 SQL> alter system set "_trace_files_public"=TRUE scope=spfile; 2 3 System altered. 4 5 SQL> startup force 6 ORACLE instance started. 7 8 Total System Global Area 330600448 bytes 9 Fixed Size 1336344 bytes10 Variable Size 226495464 bytes11 Database Buffers 96468992 bytes12 Redo Buffers 6299648 bytes13 Database mounted.14 Database opened.15 SQL> oradebug setmypid16 Statement processed.17 SQL> oradebug tracefile_name18 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_31439.trc19 SQL> ! ls -l /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_31439.trc20 -rw-r--r-- 1 oracle oinstall 3120 Feb 4 17:30 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_31439.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
1 SQL> alter session set sql_trace=true;2 3 SQL> ----execute our code4 5 SQL> alter session set sql_trace=false;
2、10046 events
1 SQL> alter session set events '10046 trace name context forever,level 1';2 3 SQL> ----execute our code4 5 SQL> alter session set events '10046 trace name context off';
另外，我们还可以使用dbms_system、dbms_support、dbms_monitor、oradebug方式进行跟踪，这些都在下面介绍。
跟踪其他会话有的时候，我们需要用DBA权限用户去跟踪某个用户的SESSION，这时候，我们先要获取SID、SERIAL#或者进程号，方法如下：
1 SQL> select sid,serial# from v$session where sid = (select sid from v$mystat where rownum=1); 2 3 SID SERIAL# 4 ---------- ---------- 5 31 111 6 7 SQL> select sid from v$mystat where rownum=1; 8 9 SID10 ----------11 3112 13 SQL> 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);14 15 PID SPID16 ---------- ------------------------17 19 523218 19 SQL> set linesize 150;20 SQL> select s.sid,s.serial#,s.username,s.osuser21 2 from v$session s,v$process p22 3 where s.paddr = p.addr;23 24 SID SERIAL# USERNAME OSUSER25 ---------- ---------- ---------- -------------26 2 1 oracle27 3 1 oracle28 4 1 oracle29 5 1 oracle30 6 1 oracle31 7 1 oracle32 8 1 oracle33 9 1 oracle34 12 1 oracle35 10 1 oracle36 11 1 oracle37 38 SID SERIAL# USERNAME OSUSER39 ---------- ---------- ----------- -------------- 40 13 1 oracle41 14 1 oracle42 15 1 oracle43 16 1 oracle44 31 111 JACK oracle45 18 9 oracle46 22 2 oracle47 28 3 oracle48 24 3 oracle49 34 31 oracle50 37 13 oracle51 52 SID SERIAL# USERNAME OSUSER53 ---------- ---------- ------------ --------------54 32 86 JACK oracle55 56 23 rows selected.
下面介绍一下DBMS_SYSTEM和ORADEBUG的方法使用。
1、DBMS_SYSTEM
DBMS_SYSTEM是Oracle 10g之前常用的跟踪作用的包，虽然我们在Oracle 10g之后的版本无法看到这个包的相关说明，但功能还是能用的。
1 -设置时间相关统计收集 2 SQL> exec dbms_system.set_bool_param_in_session(31,111,'timed_statistics',true); 3 4 -设置max_dump_file_size以trace文件大小足够容纳信息 5 SQL> exec dbms_system.set_int_param_in_session(31,111,'max_dump_file_size',20000000); 6 7 -设置10046事件并且指定LEVEL，其中''说明跟踪的是当前session 8 SQL> exec dbms_system.set_ev(31,111,10046,12,''); 9 10 -激活trace跟踪11 SQL> exec dbms_system.set_sql_trace_in_session(31,111,true);12 13 -停止trace跟踪14 SQL> exec dbms_system.set_sql_trace_in_session(31,111,false);15 16 SQL> exec dbms_system.set_ev(31,111,10046,0,'');
下面进行一个演示：
1 SQL> conn /as sysdba 2 Connected. 3 SQL> select sid, serial# from v$session where sid = (select sid from v$mystat where rownum=1); 4 5 SID SERIAL# 6 ---------- ---------- 7 38 201 8 9 SQL> exec dbms_system.set_bool_param_in_session(38,201,'timed_statistics',true);10 11 PL/SQL procedure successfully completed.12 13 SQL> exec dbms_system.set_int_param_in_session(38,201,'max_dump_file_size',20000000);14 15 PL/SQL procedure successfully completed.16 17 SQL> exec dbms_system.set_ev(38,201,10046,12,'');18 19 PL/SQL procedure successfully completed.20 21 SQL> exec dbms_system.set_sql_trace_in_session(38,201,true);22 23 PL/SQL procedure successfully completed.24 25 SQL> variable x number;26 SQL> exec :x:=127 28 PL/SQL procedure successfully completed.29 30 SQL> select count(*) from jack.jack where object_id=:x;31 32 COUNT(*)33 ----------34 035 36 SQL> exec :x:=10037 38 PL/SQL procedure successfully completed.39 40 SQL> select count(*) from jack.jack where object_id=:x;41 42 COUNT(*)43 ----------44 145 46 SQL> exec dbms_system.set_sql_trace_in_session(38,201,false);47 48 PL/SQL procedure successfully completed.49 50 SQL> exec dbms_system.set_ev(38,201,10046,0,'');51 52 PL/SQL procedure successfully completed.53 54 SQL> select value from v$diag_info where name = 'Default Trace File';55 56 VALUE57 --------------------------------------------------------------------------------58 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_6212.trc----trace文件内容： 59 60 =====================61 PARSING IN CURSOR #2 len=21 dep=0 uid=0 oct=47 lid=0 tim=1360036615100574 hv=3506322772 ad='2e7260c8' sqlid='bfjvfuv8gwgan'62 BEGIN :x:=100; END;63 END OF STMT64 PARSE #2:c=1999,e=1898,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=136003661510056965 EXEC #2:c=2000,e=2733,p=0,cr=0,cu=0,mis=1,r=1,dep=0,og=1,plh=0,tim=136003661510361466 67 *** 2013-02-05 11:56:57.46468 CLOSE #2:c=0,e=49,dep=0,type=0,tim=136003661746481369 =====================70 PARSING IN CURSOR #1 len=49 dep=0 uid=0 oct=3 lid=0 tim=1360036617465230 hv=495547162 ad='2e494808' sqlid='gp8rd4wfskwsu'71 select count(*) from jack.jack where object_id=:x72 END OF STMT73 PARSE #1:c=0,e=87,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1205023501,tim=136003661746522574 EXEC #1:c=0,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1205023501,tim=136003661746543475 FETCH #1:c=11999,e=12160,p=0,cr=1037,cu=0,mis=0,r=1,dep=0,og=1,plh=1205023501,tim=136003661747767676 STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=1037 pr=0 pw=0 time=0 us)'77 STAT #1 id=2 cnt=1 pid=1 pos=1 obj=74560 op='TABLE ACCESS FULL JACK (cr=1037 pr=0 pw=0 time=0 us cost=289 size=156 card=12)'78 FETCH #1:c=0,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1205023501,tim=136003661747879579 80 *** 2013-02-05 11:57:08.32781 CLOSE #1:c=0,e=34,dep=0,type=0,tim=136003662832786282 =====================
2、ORADEBUG
ORADEBUG功能非常强大，我们执行oradebug help将会看到非常多的功能可使用。
ORADEBUG是SYS的工具，即使权限较大的SYSTEM用户都无法使用它：
1 SQL> show user;2 USER is "SYSTEM"3 SQL> oradebug setmypid4 ORA-01031: insufficient privileges
因此，oradebug能跟踪SYS用户的当前SESSION，如下方式：
SQL> oradebug setmypidStatement processed.SQL> oradebug unlimitStatement processed.SQL> oradebug event 10046 trace name context forever,level 12Statement processed.SQL> exec our codeSQL> oradebug event 10046 trace name context offStatement processed.SQL> oradebug tracefile_name/u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5277.trc
当然，我们通过oradebug非常方便地跟踪其他会话，如下方式：
1 SQL> 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); 2 3 PID SPID 4 ---------- -------------- 5 27 5323 6 ----指定跟踪SESSION的SPID(OS process) 7 SQL> oradebug setospid 5323 8 Oracle pid: 27, Unix process pid: 5323, image: oracle@yft (TNS V1-V3) ----或者指定跟踪SESSION的PID(Oracle process ID) 9 SQL> oradebug setorapid 2710 Oracle pid: 27, Unix process pid: 5323, image: oracle@yft (TNS V1-V3)11 SQL> oradebug unlimit12 Statement processed.13 SQL> oradebug event 10053 trace name context forever,level 114 Statement processed.15 SQL> exec our code16 SQL> oradebug event 10053 trace name context off17 Statement processed.18 SQL> oradebug tracefile_name19 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5323.trc