示例相關：SQL
tname.sql
select value from v$diag_info
where name = 'Default Trace File';
sinfo.sql
select sid,serial# from v$session where sid=&sid;
spinfo.sql
select s.sid,s.serial# from v$process p,v$session s
where p.addr=s.paddr and p.spid=&pid;
sid.sql
select sid from v$mystat where rownum<2 ;
用法：
execute dbms_monitor.session_trace_enable(session_id=>&sid, serial_num=>&serial,
waits=>true,binds=>false);
關閉：
execute dbms_monitor.session_trace_disable(session_id=>&sid,serial_num=>&serial);
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
plan_stat IN VARCHAR2 DEFAULT NULL);
示例：
? 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 dexter@DEX11g> @sid SID ---------- 36 sys@DEX11g> @sinfo Enter value for sid: 36 old 1: select sid,serial#from v$session where sid=&sid new 1: select sid,serial#from v$session where sid=36 SID SERIAL# ---------- ---------- 36 415 sys@DEX11g> executedbms_monitor.session_trace_enable(session_id=>&sid,serial_num=>&serial,waits= >true,binds=>false); Enter value for sid: 36 Enter value for serial: 415 PL/SQL procedure successfully completed. dexter@DEX11g> select count(*) from t ; COUNT(*) ---------- 72523 關閉對session的跟蹤 sys@DEX11g> executedbms_monitor.session_trace_disable(session_id=>&sid,serial_num= >&serial); Enter value for sid: 36 Enter value for serial: 415 PL/SQL procedure successfully completed.用法：
exec dbms_system.set_ev(&sid,&serial,&event,&level,'&name');
若要關閉，只需要將level設置為0即可
dbms_syste.set_ev(&sid,&serial,&event,0, '&name') ;
這個方法比較通用
PROCEDURE SET_EV
Argument Name Type In/Out Default?
----------------------------------------------------- ------ --------
session_id BINARY_INTEGER IN
seriv# BINARY_INTEGER IN
event BINARY_INTEGER IN
level BINARY_INTEGER IN
name VARCHAR2 IN
示例
? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 dexter@DEX11g> @sid SID ---------- 36 sys@DEX11g> @sinfo 36 Enter value for sid: 36 old 1: select sid,serial#from v$session where sid=&sid new 1: select sid,serial#from v$session where sid=36 SID SERIAL# ---------- ---------- 36 421 sys@DEX11g> execdbms_system.set_ev(&sid,&serial,&event,&level,'&name') ; Enter value for sid: 36 Enter value for serial: 421 Enter value for event: 10046 Enter value for level: 12 Enter value for name: PL/SQL procedure successfully completed.找到相應的trace文件。注意
select value from v$diag_info
where name = 'Default Trace File';
得到的trace文件的path只是針對本session的。
比如這個例子中，使用sys用戶對sid為36的session進行跟蹤，那麼trace文件的位置可以在sid為36的
session也就是dexter用戶執行
select value from v$diag_info
where name = 'Default Trace File';
才可以得到相應的trace文件。
dexter@DEX11g> select count(*) from t ;
COUNT(*)
----------
72523
關閉（設置level=0即可）：
sys@DEX11g> execdbms_system.set_ev(&sid,&serial,&event,&level,'&name') ;
Enter value for sid: 36
Enter value for serial: 421
Enter value for event: 10046
Enter value for level: 0
Enter value for name:
PL/SQL procedure successfully completed.
用法
oradebug event 10046 trace context forever , level 12 ;
oradebug event 10046 trace context off ;
示例
oradebug setospid &pid
oradebug event 10046 trace context forever , level 12 ;
oradebug event 10046 trace context off ;
? 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 54 55 56 57 58 59 [oracle@dex ~]$ ps-aef | grep oracledex oracle 5687 5681 0 Apr05 ? 00:00:00 oracledex(DESCRIPTION=(LOCAL=YES)(ADDRESS =(PROTOCOL=beq))) oracle 6750 1 0 Apr05 ? 00:00:01 oracledex (LOCAL=NO) oracle 7785 1 0 Apr05 ? 00:00:00 oracledex (LOCAL=NO) oracle 20421 9560 0 09:35 ? 00:00:00 oracledex(DESCRIPTION=(LOCAL=YES)(ADDRESS =(PROTOCOL=beq))) oracle 20482 1 009:37 ? 00:00:00 oracledex(LOCAL=NO) oracle 20518 9234 0 09:40 pts/8 00:00:00 grep oracledex sys@DEX11g> oradebug setospid 5687 Oracle pid: 25, Unix process pid: 5687, image: oracle@dex (TNSV1-V3) sys@DEX11g> oradebug event 10046 trace name context forever ,level 12 ; Statement processed. scott@DEX11g> select * from tab ; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE 就會在trace文件中看到相應的trace select count(*) from emp END OF STMT PARSE#47316048750480:c=30996,e=154507,p=3,cr=34,cu=0,mis=1,r=0,dep=0,og=1,plh=2937609675 ,tim=1365212576667998 EXEC#47316048750480:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2937609675,tim =1365212576668227 WAIT #47316048750480: nam='SQL*Net message to client' ela= 11 driverid=1650815232 #bytes =1 p3=0 obj#=40 tim=1365212576668455 FETCH #47316048750480:c=999,e=21005,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2937609675,tim = 1365212576689528 STAT #47316048750480 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE(cr=1 pr=0 pw= 0 time=20996 us)' STAT #47316048750480 id=2 cnt=15 pid=1 pos=1 obj=75336 op='INDEXFULL SCAN PK_EMP (cr=1 pr=0 pw=0 time=20960 us cost=1 size=0 card=15)' WAIT #47316048750480: nam='SQL*Net message from client' ela= 560driver id=1650815232 #bytes=1 p3=0 obj#=40 tim=1365212576690779 FETCH #47316048750480:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2937609675,tim =1365212576690879 WAIT #47316048750480: nam='SQL*Net message to client' ela= 6 driverid=1650815232 #bytes=1 p3=0 obj#=40 tim=1365212576690934 WAIT #47316048750480: nam='SQL*Net message from client' ela= 834driver id=16508 15232 #bytes=1 p3=0 obj#=40 tim=1365212576691804 PARSE#47316048799960:c=0,e=75,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim= 1365212576691987 BINDS #47316048799960: Bind#0 oacdty=123 mxl=4000(4000)mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=1000000 frm=00csi=00 siz=4000 off=0 toid ptr value=87B716C0 length=16 AD26DE2F1F4C7C06E0431E4EE50AB7B3 kxsbbbfp=2b08a050c4d0 bln=4000 avl=00 flg=15 Bind#1 oacdty=02 mxl=22(22) mxlc=00mal=00 scl=00 pre=00 oacflg=01 fl2=1000000 frm=00csi=00 siz=24 off=0 kxsbbbfp=2b08a054bb00 bln=22 avl=22 flg=05 value=### An invalid number has beenseen.Memory contents are : Dump of memory from 0x00002B08A054BB00 to 0x00002B08A054BB16 2B08A054BB00 000010C1 00000000 00000000 00000000 [................] 2B08A054BB10 00000000 00000000 [........] WAIT #47316048799960: nam='SQL*Net message to client' ela= 9 driverid=1650815232 #bytes=1 p3=0 obj#=40 tim=1365212576693088 EXEC#47316048799960:c=999,e=1002,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=13652 12576693170 關閉 sys@DEX11g> oradebug event 10046 trace name context off ; Statement processed.用法
alter system set events ‘sql_trace {process:&pid} level 12’;
alter system set events ‘sql_trace {process:&pid} off’;
（關閉的時候有延遲）
示例
? 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 [oracle@dex trace]$ ps -aef | grep oracledex oracle 6750 1 0 Apr05 ? 00:00:01 oracledex (LOCAL=NO) oracle 7785 1 0 Apr05 ? 00:00:00 oracledex (LOCAL=NO) oracle 20482 1 0 09:37 ? 00:00:00 oracledex (LOCAL=NO) oracle 20730 20727 0 09:53 ? 00:00:00 oracledex (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 20752 20746 0 09:54 ? 00:00:00 oracledex (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 20757 9610 0 09:54 pts/3 00:00:00 grep oracledex sys@DEX11g> alter system set events 'sql_trace {process:20752} level 12'; System altered. dexter@DEX11g> select * from tab ; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- RUN_STATS TABLE STATS VIEW T TABLE TS TABLE sys@DEX11g> alter system set events 'sql_trace {process:20752} off'; System altered.用法
alter system set events 'sql_Trace[sql:&sql_id] level 12' ;
alter system set events 'sql_Trace[sql:&sql_id] off' ;
示例
? 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 sys@DEX11g> select sql_text , sql_id from v$sql where sql_textlike '%from t '; SQL_TEXT SQL_ID ------------------------------------------------------------------------- selectmin(object_id) from t gp2gyxwx140jx select count(*) from t 45vdc2q5hs1f3 select count(*) from t 45vdc2q5hs1f3 select avg(object_id) from t 1xbrzw9w1m9rf select avg(object_id) from t 1xbrzw9w1m9rf select sum(object_id) from t 3jpp2an783paa select sum(object_id) from t 3jpp2an783paa 7 rows selected. sys@DEX11g> alter system set events 'sql_Trace[sql:&sql_id]level 12' ; Enter value for sql_id: gp2gyxwx140jx old 1: alter system setevents 'sql_Trace[sql:&sql_id] level 12' new 1: alter system setevents 'sql_Trace[sql:gp2gyxwx140jx] level 12' System altered. dexter@DEX11g> select min(object_id) from t ; MIN(OBJECT_ID) -------------- 2 sys@DEX11g> alter system set events 'sql_Trace[sql:&sql_id]off' ; Enter value for sql_id: gp2gyxwx140jx old 1: alter system setevents 'sql_Trace[sql:&sql_id] off' new 1: alter system setevents 'sql_Trace[sql:gp2gyxwx140jx] off' System altered.注：
因為trace文件是和session關聯的，所以不同session執行sql_id為gp2gyxwx140jx的語句的時候，會生成多個trace文件。
當關閉sql_id的trace的時候，對已經連接的session來說，不受影響（還是會生成trace信息），對於新建立連接的session生效。
