Oracle SQL trace 不同level 的区别
浏览数：31 /
时间：2015年06月12日
|
Level |
Waits |
Binds |
|
1 |
False |
False |
|
4 |
False |
True |
|
8 |
True |
False |
|
12 |
True |
True |
对于同一条语句
sys@DEX11g> var oname varchar2(200) ;
sys@DEX11g> exec :oname := ‘PUBLIC‘ ;
PL/SQL procedure successfully completed.
sys@DEX11g> oradebug setmypid
Statement processed.
sys@DEX11g> oradebug event 10046 trace name context forever , level 1;
Statement processed.
sys@DEX11g> select count(*) from dexter.t where owner=:oname ;
COUNT(*)
----------
28027
sys@DEX11g> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/dex/dex/trace/dex_ora_21099.trc
sys@DEX11g> oradebug event 10046 trace name context forever , level 4;
Statement processed.
sys@DEX11g> select count(*) from dexter.t where owner=:oname ;
COUNT(*)
----------
28027
sys@DEX11g> oradebug event 10046 trace name context forever , level 8;
Statement processed.
sys@DEX11g> select count(*) from dexter.t where owner=:oname ;
COUNT(*)
----------
28027
sys@DEX11g> oradebug event 10046 trace name context forever , level 12;
Statement processed.
sys@DEX11g> exec :oname := ‘SYS‘ ;
PL/SQL procedure successfully completed.
sys@DEX11g> select sum(object_id) from dexter.t where owner=:oname ;
SUM(OBJECT_ID)
--------------
870461706
level 1
只有基本的一些关于sql语句执行信息
PARSING IN CURSOR #47769055467368 len=49 dep=0 uid=0 oct=3 lid=0 tim=1365214739408233 hv=1571021352 ad=‘861fc700‘ sqlid=‘fk8k4v1fu7sj8‘ select count(*) from dexter.t where owner=:oname END OF STMT PARSE #47769055467368:c=1000,e=1475,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1365214739408230 EXEC #47769055467368:c=6998,e=6678,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2966233522,tim=1365214739415599 *** 2013-04-06 10:19:00.761 FETCH #47769055467368:c=354946,e=1345404,p=1033,cr=1035,cu=0,mis=0,r=1,dep=0,og=1,plh=2966233522,tim=1365214740761236 STAT #47769055467368 id=1 cnt=1 pid=0 pos=1 obj=0 op=‘SORT AGGREGATE (cr=1035 pr=1033 pw=0 time=1345423 us)‘ STAT #47769055467368 id=2 cnt=28027 pid=1 pos=1 obj=77141 op=‘TABLE ACCESS FULL T (cr=1035 pr=1033 pw=0 time=186082 us cost=290 size=14502 card=2417)‘ FETCH #47769055467368:c=0,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2966233522,tim=1365214740762714
level 4
包括了绑定变量的信息，但是没有等待事件的统计。
*** 2013-04-06 10:19:32.644
=====================
PARSING IN CURSOR #47769055467368 len=49 dep=0 uid=0 oct=3 lid=0 tim=1365214772644926 hv=1571021352 ad=‘861fc700‘ sqlid=‘fk8k4v1fu7sj8‘
select count(*) from dexter.t where owner=:oname
END OF STMT
PARSE #47769055467368:c=999,e=311,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2966233522,tim=1365214772644923
BINDS #47769055467368:
Bind#0
oacdty=01 mxl=2000(400) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=852 siz=2000 off=0
kxsbbbfp=2b72199594b0 bln=2000 avl=06 flg=05
value="PUBLIC"
EXEC #47769055467368:c=1000,e=499,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2966233522,tim=1365214772645705
*** 2013-04-06 10:19:32.689
FETCH #47769055467368:c=24997,e=43476,p=1033,cr=1035,cu=0,mis=0,r=1,dep=0,og=1,plh=2966233522,tim=1365214772689301
STAT #47769055467368 id=1 cnt=1 pid=0 pos=1 obj=0 op=‘SORT AGGREGATE (cr=1035 pr=1033 pw=0 time=43518 us)‘
STAT #47769055467368 id=2 cnt=28027 pid=1 pos=1 obj=77141 op=‘TABLE ACCESS FULL T (cr=1035 pr=1033 pw=0 time=15880 us cost=290 size=14502 card=2417)‘
FETCH #47769055467368:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2966233522,tim=1365214772690621
PARSE #47769055501464:c=0,e=240,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1365214772692158
BINDS #47769055501464:
Bind#0
oacdty=123 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000000 frm=00 csi=00 siz=4000 off=0
toid ptr value=87B716C0 length=16
AD26DE2F1F4C7C06E0431E4EE50AB7B3
kxsbbbfp=2b72199588c8 bln=4000 avl=00 flg=15
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b7219959c68 bln=22 avl=22 flg=05
value=###
An invalid number has been seen.Memory contents are :
Dump of memory from 0x00002B7219959C68 to 0x00002B7219959C7E
2B7219959C60 000010C1 00000000 [........]
2B7219959C70 00000000 00000000 00000000 00000000 [................]
EXEC #47769055501464:c=2000,e=1550,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1365214772693876
evel 8
包括等待时间的信息，但是不包括绑定变量的信息
PARSING IN CURSOR #47769055467368 len=49 dep=0 uid=0 oct=3 lid=0 tim=1365214778550346 hv=1571021352 ad=‘861fc700‘ sqlid=‘fk8k4v1fu7sj8‘ select count(*) from dexter.t where owner=:oname END OF STMT PARSE #47769055467368:c=0,e=213,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2966233522,tim=1365214778550343 EXEC #47769055467368:c=0,e=88,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2966233522,tim=1365214778550849 WAIT #47769055467368: nam=‘SQL*Net message to client‘ ela= 12 driver id=1650815232 #bytes=1 p3=0 obj#=77141 tim=1365214778550986 WAIT #47769055467368: nam=‘direct path read‘ ela= 144 file number=4 first dba=523 block cnt=13 obj#=77141 tim=1365214778551487 WAIT #47769055467368: nam=‘direct path read‘ ela= 567 file number=4 first dba=537 block cnt=15 obj#=77141 tim=1365214778552290 WAIT #47769055467368: nam=‘direct path read‘ ela= 71 file number=4 first dba=553 block cnt=15 obj#=77141 tim=1365214778552590 WAIT #47769055467368: nam=‘direct path read‘ ela= 75 file number=4 first dba=569 block cnt=15 obj#=77141 tim=1365214778552969 WAIT #47769055467368: nam=‘direct path read‘ ela= 76 file number=4 first dba=585 block cnt=15 obj#=77141 tim=1365214778553554 WAIT #47769055467368: nam=‘direct path read‘ ela= 162 file number=4 first dba=601 block cnt=15 obj#=77141 tim=1365214778553924 WAIT #47769055467368: nam=‘direct path read‘ ela= 89 file number=4 first dba=617 block cnt=15 obj#=77141 tim=1365214778554252 WAIT #47769055467368: nam=‘direct path read‘ ela= 53 file number=4 first dba=633 block cnt=7 obj#=77141 tim=1365214778555146 WAIT #47769055467368: nam=‘direct path read‘ ela= 57 file number=4 first dba=8832 block cnt=8 obj#=77141 tim=1365214778555479 WAIT #47769055467368: nam=‘direct path read‘ ela= 1622 file number=4 first dba=8962 block cnt=62 obj#=77141 tim=1365214778557242 WAIT #47769055467368: nam=‘direct path read‘ ela= 405 file number=4 first dba=9024 block cnt=64 obj#=77141 tim=1365214778558052 WAIT #47769055467368: nam=‘direct path read‘ ela= 287 file number=4 first dba=9090 block cnt=62 obj#=77141 tim=1365214778559326 WAIT #47769055467368: nam=‘direct path read‘ ela= 504 file number=4 first dba=9152 block cnt=64 obj#=77141 tim=1365214778560717 WAIT #47769055467368: nam=‘direct path read‘ ela= 2238 file number=4 first dba=9218 block cnt=62 obj#=77141 tim=1365214778563809 WAIT #47769055467368: nam=‘direct path read‘ ela= 116 file number=4 first dba=9280 block cnt=64 obj#=77141 tim=1365214778564291 WAIT #47769055467368: nam=‘direct path read‘ ela= 104 file number=4 first dba=9346 block cnt=62 obj#=77141 tim=1365214778564767 WAIT #47769055467368: nam=‘direct path read‘ ela= 88 file number=4 first dba=9408 block cnt=64 obj#=77141 tim=1365214778565239 WAIT #47769055467368: nam=‘direct path read‘ ela= 123 file number=4 first dba=9474 block cnt=62 obj#=77141 tim=1365214778565588 WAIT #47769055467368: nam=‘direct path read‘ ela= 85 file number=4 first dba=9536 block cnt=64 obj#=77141 tim=1365214778565908 WAIT #47769055467368: nam=‘direct path read‘ ela= 111 file number=4 first dba=9602 block cnt=62 obj#=77141 tim=1365214778566363 WAIT #47769055467368: nam=‘direct path read‘ ela= 84 file number=4 first dba=9664 block cnt=64 obj#=77141 tim=1365214778566672 WAIT #47769055467368: nam=‘direct path read‘ ela= 105 file number=4 first dba=9730 block cnt=62 obj#=77141 tim=1365214778566982 WAIT #47769055467368: nam=‘direct path read‘ ela= 86 file number=4 first dba=9792 block cnt=64 obj#=77141 tim=1365214778567389 WAIT #47769055467368: nam=‘direct path read‘ ela= 48 file number=4 first dba=9858 block cnt=33 obj#=77141 tim=1365214778567664 FETCH #47769055467368:c=15997,e=16853,p=1033,cr=1035,cu=0,mis=0,r=1,dep=0,og=1,plh=2966233522,tim=1365214778567899 STAT #47769055467368 id=1 cnt=1 pid=0 pos=1 obj=0 op=‘SORT AGGREGATE (cr=1035 pr=1033 pw=0 time=16849 us)‘ STAT #47769055467368 id=2 cnt=28027 pid=1 pos=1 obj=77141 op=‘TABLE ACCESS FULL T (cr=1035 pr=1033 pw=0 time=12512 us cost=290 size=14502 card=2417)‘ WAIT #47769055467368: nam=‘SQL*Net message from client‘ ela= 243 driver id=1650815232 #bytes=1 p3=0 obj#=77141 tim=1365214778568306 FETCH #47769055467368:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2966233522,tim=1365214778568341 WAIT #47769055467368: nam=‘SQL*Net message to client‘ ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=77141 tim=1365214778568361 WAIT #47769055467368: nam=‘SQL*Net message from client‘ ela= 298 driver id=1650815232 #bytes=1 p3=0 obj#=77141 tim=1365214778568672 PARSE #47769055501464:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1365214778568752 WAIT #47769055501464: nam=‘SQL*Net message to client‘ ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=77141 tim=1365214778569032 EXEC #47769055501464:c=0,e=258,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1365214778569066
level 12
包括所有等待事件的统计和绑定变量的信息。
PARSING IN CURSOR #47769055380448 len=55 dep=0 uid=0 oct=3 lid=0 tim=1365215140961263 hv=2496234691 ad=‘843636f8‘ sqlid=‘24av8d6acm163‘ select sum(object_id) from dexter.t where owner=:oname END OF STMT PARSE #47769055380448:c=2999,e=1855,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1365215140961260 BINDS #47769055380448: Bind#0 oacdty=01 mxl=2000(400) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=852 siz=2000 off=0 kxsbbbfp=2b72199594b0 bln=2000 avl=03 flg=05 value="SYS" EXEC #47769055380448:c=3000,e=3793,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2966233522,tim=1365215140965285 WAIT #47769055380448: nam=‘SQL*Net message to client‘ ela= 16 driver id=1650815232 #bytes=1 p3=0 obj#=77141 tim=1365215140965453 WAIT #47769055380448: nam=‘asynch descriptor resize‘ ela= 10 outstanding #aio=0 current aio limit=152 new aio limit=182 obj#=77141 tim=1365215140965882 WAIT #47769055380448: nam=‘direct path read‘ ela= 227 file number=4 first dba=523 block cnt=13 obj#=77141 tim=1365215140966341 WAIT #47769055380448: nam=‘direct path read‘ ela= 474 file number=4 first dba=537 block cnt=15 obj#=77141 tim=1365215140967633 WAIT #47769055380448: nam=‘direct path read‘ ela= 164 file number=4 first dba=553 block cnt=15 obj#=77141 tim=1365215140968680 WAIT #47769055380448: nam=‘direct path read‘ ela= 508 file number=4 first dba=569 block cnt=15 obj#=77141 tim=1365215140969663 WAIT #47769055380448: nam=‘direct path read‘ ela= 478 file number=4 first dba=585 block cnt=15 obj#=77141 tim=1365215140970818 WAIT #47769055380448: nam=‘direct path read‘ ela= 176 file number=4 first dba=601 block cnt=15 obj#=77141 tim=1365215140971762 WAIT #47769055380448: nam=‘direct path read‘ ela= 75 file number=4 first dba=617 block cnt=15 obj#=77141 tim=1365215140972313 WAIT #47769055380448: nam=‘direct path read‘ ela= 47 file number=4 first dba=633 block cnt=7 obj#=77141 tim=1365215140973455 WAIT #47769055380448: nam=‘direct path read‘ ela= 62 file number=4 first dba=8832 block cnt=8 obj#=77141 tim=1365215140974061 WAIT #47769055380448: nam=‘direct path read‘ ela= 1412 file number=4 first dba=8962 block cnt=62 obj#=77141 tim=1365215140975874 WAIT #47769055380448: nam=‘direct path read‘ ela= 568 file number=4 first dba=9024 block cnt=64 obj#=77141 tim=1365215140976990 WAIT #47769055380448: nam=‘direct path read‘ ela= 566 file number=4 first dba=9090 block cnt=62 obj#=77141 tim=1365215140979413 WAIT #47769055380448: nam=‘direct path read‘ ela= 463 file number=4 first dba=9152 block cnt=64 obj#=77141 tim=1365215140981749 WAIT #47769055380448: nam=‘direct path read‘ ela= 597 file number=4 first dba=9218 block cnt=62 obj#=77141 tim=1365215140984267 WAIT #47769055380448: nam=‘direct path read‘ ela= 419 file number=4 first dba=9280 block cnt=64 obj#=77141 tim=1365215140986349 WAIT #47769055380448: nam=‘direct path read‘ ela= 268 file number=4 first dba=9346 block cnt=62 obj#=77141 tim=1365215140988105 WAIT #47769055380448: nam=‘direct path read‘ ela= 589 file number=4 first dba=9408 block cnt=64 obj#=77141 tim=1365215140990125 WAIT #47769055380448: nam=‘direct path read‘ ela= 311 file number=4 first dba=9474 block cnt=62 obj#=77141 tim=1365215140991845 WAIT #47769055380448: nam=‘direct path read‘ ela= 392 file number=4 first dba=9536 block cnt=64 obj#=77141 tim=1365215140993754 WAIT #47769055380448: nam=‘direct path read‘ ela= 450 file number=4 first dba=9602 block cnt=62 obj#=77141 tim=1365215140995500 WAIT #47769055380448: nam=‘direct path read‘ ela= 526 file number=4 first dba=9664 block cnt=64 obj#=77141 tim=1365215140997729 WAIT #47769055380448: nam=‘direct path read‘ ela= 97 file number=4 first dba=9730 block cnt=62 obj#=77141 tim=1365215140998471 WAIT #47769055380448: nam=‘direct path read‘ ela= 141 file number=4 first dba=9792 block cnt=64 obj#=77141 tim=1365215140998813 WAIT #47769055380448: nam=‘direct path read‘ ela= 108 file number=4 first dba=9858 block cnt=33 obj#=77141 tim=1365215140999155 FETCH #47769055380448:c=33995,e=33888,p=1033,cr=1035,cu=0,mis=0,r=1,dep=0,og=1,plh=2966233522,tim=1365215140999410 STAT #47769055380448 id=1 cnt=1 pid=0 pos=1 obj=0 op=‘SORT AGGREGATE (cr=1035 pr=1033 pw=0 time=33879 us)‘ STAT #47769055380448 id=2 cnt=31142 pid=1 pos=1 obj=77141 op=‘TABLE ACCESS FULL T (cr=1035 pr=1033 pw=0 time=84280 us cost=290 size=26587 card=2417)‘ WAIT #47769055380448: nam=‘SQL*Net message from client‘ ela= 289 driver id=1650815232 #bytes=1 p3=0 obj#=77141 tim=1365215140999832 FETCH #47769055380448:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2966233522,tim=1365215140999873 WAIT #47769055380448: nam=‘SQL*Net message to client‘ ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=77141 tim=1365215140999895 WAIT #47769055380448: nam=‘SQL*Net message from client‘ ela= 541 driver id=1650815232 #bytes=1 p3=0 obj#=77141 tim=1365215141000449
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
