玩轉跟蹤(to owner session、other session)

如今，一般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或者我寫的一篇文章《》

 

 

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