OracleSQLTrace几种不同方法示例

　　示例相关：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 ;

　　已知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

　　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.

　　

　　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

　　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.

　　已知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

　　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.

　　

　　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

　　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.

　　

　　已知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_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生效。

分享到：