示例相关：SQL
tname.sql
select value from v$diag_info
where name = 'Default Trace File';
sinfo.sql
select sid,serial# from v$session where sid=
spinfo.sql
select s.sid,s.serial# from v$process p,v$session s
where p.addr=s.paddr and p.spid=
sid.sql
select sid from v$mystat where rownum<2 ;
已知session ID 对其进行跟踪
dbms_monitor
用法：
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
old1: select sid,serial#from v$session where sid=&sid
new1: select sid,serial#from v$session where sid=36
SIDSERIAL#
---------- ----------
36415
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.
dbms_system
用法：
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
old1: select sid,serial#from v$session where sid=&sid
new1: select sid,serial#from v$session where sid=36
SIDSERIAL#
---------- ----------
36421
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.
已知os pid 对其进行跟踪
oradebug
用法
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
oracle568756810 Apr05 ?00:00:00 oracledex(DESCRIPTION=(LOCAL=YES)(ADDRESS
=(PROTOCOL=beq)))
oracle675010 Apr05 ?00:00:01 oracledex (LOCAL=NO)
oracle778510 Apr05 ?00:00:00 oracledex (LOCAL=NO)
oracle20421 95600 09:35 ?00:00:00 oracledex(DESCRIPTION=(LOCAL=YES)(ADDRESS
=(PROTOCOL=beq)))
oracle204821009:37 ?00:00:00 oracledex(LOCAL=NO)
oracle20518 92340 09:40 pts/800: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 ;
TNAMETABTYPECLUSTERID
------------------------------ ------- ----------
BONUSTABLE
DEPTTABLE
EMPTABLE
SALGRADETABLE
就会在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=2b08a050c4d0bln=4000 avl=00flg=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=2b08a054bb00bln=22 avl=22flg=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.
sql_trace
用法
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
oracle675010 Apr05 ?00:00:01 oracledex (LOCAL=NO)
oracle778510 Apr05 ?00:00:00 oracledex (LOCAL=NO)
oracle2048210 09:37 ?00:00:00 oracledex (LOCAL=NO)
oracle20730 207270 09:53 ?00:00:00 oracledex (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle20752 207460 09:54 ?00:00:00 oracledex (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle2075796100 09:54 pts/300:00:00 grep oracledex
sys@DEX11g> alter system set events 'sql_trace {process:20752} level 12';
System altered.
dexter@DEX11g> select * from tab ;
TNAMETABTYPECLUSTERID
------------------------------ ------- ----------
RUN_STATSTABLE
STATSVIEW
TTABLE
TSTABLE
sys@DEX11g> alter system set events 'sql_trace {process:20752} off';
System altered.
已知sql_id对其进行跟踪
sql_trace
用法
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_TEXTSQL_ID
-------------------------------------------------------------------------
selectmin(object_id) from tgp2gyxwx140jx
select count(*) from t45vdc2q5hs1f3
select count(*) from t45vdc2q5hs1f3
select avg(object_id) from t1xbrzw9w1m9rf
select avg(object_id) from t1xbrzw9w1m9rf
select sum(object_id) from t3jpp2an783paa
select sum(object_id) from t3jpp2an783paa
7 rows selected.
sys@DEX11g> alter system set events 'sql_Trace[sql:&sql_id]level 12' ;
Enter value for sql_id: gp2gyxwx140jx
old1: alter system setevents 'sql_Trace[sql:&sql_id] level 12'
new1: 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
old1: alter system setevents 'sql_Trace[sql:&sql_id] off'
new1: alter system setevents 'sql_Trace[sql:gp2gyxwx140jx] off'
System altered.
注：
因为trace文件是和session关联的，所以不同session执行sql_id为gp2gyxwx140jx的语句的时候，会生成多个trace文件。
当关闭sql_id的trace的时候，对已经连接的session来说，不受影响（还是会生成trace信息），对于新建立连接的session生效。
