OracleSQLtrace不同level的区别(一)

对于同一条语句

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