OracleSQLTrace几种不同方法示例 - 数据库编程

ext 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’;
（关闭的时候有延
OracleSQLTrace几种不同方法示例(二)

sql_trace
