如今，一般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}如下查看：
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内容略。
– 当前会话跟踪
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('www.luocs.com');
3 DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE
4 (CLIENT_ID => 'www.luocs.com',
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('www.luocs.com');
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, p_w_picpath: oracle@maa3.luocs.com (TNS V1-V3)
-- 或者指定跟踪SESSION的PID(Oracle process ID)
sys@MAA> oradebug setorapid 25
Oracle pid: 25, Unix process pid: 17678, p_w_picpath: 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
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
转载于:https://blog.51cto.com/19880614/1259482
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
