oracle sql trace案例,Oracle SQL Trace 几种不同方法示例

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

示例：

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

示例

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 ;

[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=1650815232 #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=1365212576693170

关闭

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’;

(关闭的时候有延迟)

示例

[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' ;

示例

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生效。

如需转载，请注明出处：

blog.csdn.net/renfengjun 或者

www.orcl.cc