untuk trace2 yang mungkin berguna, coba ke site ini : https://netfiles.uiuc.edu/jstrode/www/oraparm/EventReference.html
regards, pandu wp --- On Wed, 1/28/09, Ujang Jaenudin <[email protected]> wrote: From: Ujang Jaenudin <[email protected]> Subject: Re: [indo-oracle] Re: cara kerja b-tree index pada kasus insert 1000 data To: [email protected] Date: Wednesday, January 28, 2009, 9:37 PM hanya melanjutkan dari wandi saja, di trace 10046.... ada inidikasi recursive SQL yg memaintain index sewaktu insert dilakukan... antara lain (tapi recursive ini hanya memaintain data dictionary.. ..pengisian indexnya sendiri tidak tertangkap oleh trace 10046): update tsq$ set blocks=:3,maxblocks =:4,grantor# =:5,priv1= :6,priv2= :7,priv3= :8 where ts#=:1 and user#=:2 update seg$ set type#=:4,blocks= :5,extents= :6,minexts= :7,maxexts= :8,extsize= :9,extpct= :10,user# =:11,iniexts= :12,lists= decode(:13, 65535, NULL, :13),groups= de berikut ini indikasi lain dgn index tree dump: ALTER SESSION SET EVENTS 'immediate trace name treedump level 52897'; begin for i in 1 .. 1000 loop insert into abcd_test values (i,'Nama_'|| i); end loop; commit; end; ----- begin tree dump branch: 0x10001b4 16777652 (0: nrow: 2, level: 1) leaf: 0x10001b6 16777654 (-1: nrow: 540 rrow: 540) leaf: 0x10001b7 16777655 (0: nrow: 460 rrow: 460) ----- end tree dump *** 2009-01-29 11:30:21.890 begin for i in 1 .. 1000 loop insert into abcd_test values (i,'Nama_'|| i); end loop; commit; end; ----- begin tree dump branch: 0x10001b4 16777652 (0: nrow: 5, level: 1) leaf: 0x10001b7 16777655 (-1: nrow: 284 rrow: 284) leaf: 0x10001b6 16777654 (0: nrow: 272 rrow: 272) leaf: 0x10001b5 16777653 (1: nrow: 524 rrow: 524) leaf: 0x10001b8 16777656 (2: nrow: 396 rrow: 396) leaf: 0x10001bf 16777663 (3: nrow: 524 rrow: 524) ----- end tree dump begin for i in 1 .. 1000 loop insert into abcd_test values (i,'Nama_'|| i); end loop; end; ----- begin tree dump branch: 0x10001b4 16777652 (0: nrow: 8, level: 1) leaf: 0x10001b7 16777655 (-1: nrow: 426 rrow: 426) leaf: 0x10001b6 16777654 (0: nrow: 408 rrow: 408) leaf: 0x10001b5 16777653 (1: nrow: 393 rrow: 393) leaf: 0x10001c0 16777664 (2: nrow: 393 rrow: 393) leaf: 0x10001b8 16777656 (3: nrow: 270 rrow: 270) leaf: 0x10001b9 16777657 (4: nrow: 324 rrow: 324) leaf: 0x10001bf 16777663 (5: nrow: 393 rrow: 393) leaf: 0x10001ba 16777658 (6: nrow: 393 rrow: 393) ----- end tree dump rollback ----- begin tree dump branch: 0x10001b4 16777652 (0: nrow: 8, level: 1) leaf: 0x10001b7 16777655 (-1: nrow: 284 rrow: 284) leaf: 0x10001b6 16777654 (0: nrow: 272 rrow: 272) leaf: 0x10001b5 16777653 (1: nrow: 262 rrow: 262) leaf: 0x10001c0 16777664 (2: nrow: 262 rrow: 262) leaf: 0x10001b8 16777656 (3: nrow: 180 rrow: 180) leaf: 0x10001b9 16777657 (4: nrow: 216 rrow: 216) leaf: 0x10001bf 16777663 (5: nrow: 262 rrow: 262) leaf: 0x10001ba 16777658 (6: nrow: 262 rrow: 262) ----- end tree dump dari index tree dump tsb , disimpulkan: - oracle akan memaintain index, pada saat DML dilakukan pada table sebelum transaksi di commit. - untuk b-tree index, akan selalu dijaga tree balance (bisa dilihat dari leaf block yg selalu balance) - jika transaksi DML di rollback, leaf block yg sudah diallocate tidak serta merta dideallocate, row-row data yg sebelumnya mengisi leaf block tsb, dihapus - untuk process split leaf block nya sendiri, bisa dibuktikan dgn event 10224 ALTER SESSION SET EVENTS '10224 trace name context forever, level 1'; berikut sample output trace nya sendiri: splitting leaf,dba 0x010001b7,time 12:31:56.250 kdisnew_bseg_ srch_cbk using block,dba 0x010001bc,time 12:31:56.250 splitting leaf,dba 0x010001b6,time 12:31:56.281 kdisnew_bseg_ srch_cbk reject block -mark full,dba 0x010001bc,time 12:31:56.281 kdisnew_bseg_ srch_cbk rejecting block ,dba 0x010001bc,time 12:31:56.281 kdisnew_bseg_ srch_cbk using block,dba 0x010001bd,time 12:31:56.281 -- thanks and regards ujang | oracle dba | mysql dba pt. visi solusi teknologi | jakarta http://www.visitek. co.id http://ora62. wordpress. com On 1/29/09, wandi_ms <wandi...@yahoo. com> wrote: > > Bang Erwin, > > Dari oracle, mungkin bang Erwin bisa memanfaatkan fasilitas tracenya > oracle, dengan set trace level = 12. Namun, pertanyaan selanjutnya > apakah MSSQL juga menyediakan fasilitas trace yg boleh di katakan "open" > seopen fasilitasnya oracle... :-). > > Untuk sedikit tambahan di kotretan skripsi-nya bang Andes...hehehe, di > bawah ini test yg sy lakukan : > > t...@dev05 <mailto:test@ DEV05> > create table abcd_test > 2 (no number, > 3 nama varchar2(40) ); > > Table created. > > t...@dev05 <mailto:test@ DEV05> > create index abcd_test_idx1 > 2 on abcd_test(no) > 3 tablespace users_idx; > > Index created. > > t...@dev05 <mailto:test@ DEV05> > alter session set events '10046 trace > name context forever, level 12'; > > Session altered. > > t...@dev05 <mailto:test@ DEV05> > begin > 2 for i in 1 .. 1000 > 3 loop > 4 insert into abcd_test > 5 values (i,'Nama_'|| i); > 6 end loop; > 7 commit; > 8 end; > 9 / > > PL/SQL procedure successfully completed. > > t...@dev05 <mailto:test@ DEV05> > select count(*) from abcd_test; > > COUNT(*) > ---------- > 1000 > > t...@dev05 <mailto:test@ DEV05> > alter session set events '10046 trace > name context off'; > > Session altered. > > > Dan summary dari isi trace di atas adalah : > > Dump file d:\oracle\product\ 10.1.0\admin\ dev05\udump\ dev05_ora_ 1420.trc > Thu Jan 29 10:35:35 2009 > ORACLE V10.1.0.2.0 - Production vsnsta=0 > vsnsql=13 vsnxtr=3 > Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production > With the Partitioning, OLAP and Data Mining options > Windows XP Version V5.1 Service Pack 2 > CPU : 1 - type 586 > Process Affinity: 0x00000000 > Memory (A/P) : PH:91M/1021M, PG:1398M/2464M, VA:1632M/2047M > Instance name: dev05 > > Redo thread mounted by this instance: 1 > > Oracle process number: 21 > > Windows thread id: 1420, image: ORACLE.EXE (SHAD) > > > *** 2009-01-29 10:35:35.674 > *** ACTION NAME:() 2009-01-29 10:35:35.596 > *** MODULE NAME:(SQL*Plus) 2009-01-29 10:35:35.596 > *** SERVICE NAME:(SYS$USERS) 2009-01-29 10:35:35.596 > *** SESSION ID:(142.3023) 2009-01-29 10:35:35.596 > ============ ========= > PARSING IN CURSOR #16 len=69 dep=0 uid=448 oct=42 lid=448 tim=7900874275 > hv=3164292706 ad='1b1768a4' > alter session set events '10046 trace name context forever, level 12' > END OF STMT > EXEC #16:c=0,e=20097, p=0,cr=0, cu=0,mis= 1,r=0,dep= 0,og=1,tim= 7900874261 > WAIT #16: nam='SQL*Net message to client' ela= 7 p1=1111838976 p2=1 p3=0 > *** 2009-01-29 10:36:22.254 > WAIT #16: nam='SQL*Net message from client' ela= 46584401 p1=1111838976 > p2=1 p3=0 > ============ ========= > PARSING IN CURSOR #11 len=195 dep=1 uid=0 oct=3 lid=0 tim=7947590810 > hv=1782190218 ad='1d70cfe4' > select obj#,type#,ctime, mtime,stime, status,dataobj# ,flags,oid$ , spare1, > spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and > remoteowner is null and linkname is null and subname = :4 > END OF STMT > PARSE #11:c=0,e=705, p=0,cr=0, cu=0,mis= 1,r=0,dep= 1,og=4,tim= 7947590804 > BINDS #11: > bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=0001 > size=24 offset=0 > bfp=06cd6e4c bln=22 avl=03 flg=05 > value=448 > bind 1: dty=1 mxl=32(09) mal=00 scl=00 pre=00 oacflg=18 oacfl2=0001 > size=32 offset=0 > bfp=06cd6d08 bln=32 avl=09 flg=05 > value="ABCD_ TEST" > bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=0001 > size=24 offset=0 > bfp=06cd6b04 bln=24 avl=02 flg=05 > value=1 > bind 3: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=18 oacfl2=0001 > size=32 offset=0 > bfp=06ce0e40 bln=32 avl=05 flg=05 > value="DIANA" > EXEC > #11:c=15625, e=83771,p= 0,cr=0,cu= 0,mis=1,r= 0,dep=1,og= 4,tim=7947689465 > FETCH #11:c=0,e=50, p=0,cr=2, cu=0,mis= 0,r=0,dep= 1,og=4,tim= 7947707995 > ============ ========= > PARSING IN CURSOR #14 len=1664 dep=1 uid=0 oct=3 lid=0 tim=7947716962 > hv=610427736 ad='1d6fcff8' > select '"'||name||' " > '||decode(type# ,1,decode( charsetform, 2,'NVARCHAR2( '||decode( bitand(prope\ > rty,8388608) ,8388608, decode(spare3, 0,1,spare3) ||')','BYTECOUNT =>'||decod\ > e(length,0,1, length)|| ')'),'VARCHAR2( '||decode( bitand(property, 8388608), > \ > 8388608,decode( spare3,0, 1,spare3) ||' char)',decode( length,0, 1,length) ||' > byte)')),2,decode( scale,null, decode(precision #,null,'NUMBER' ,'FLOAT(' ||p\ > recision#||' )'),'NUMBER( '||decode( precision# ,null,'38' , > precision#)| |','||scale| |')'),8,' LONG',9,' VARCHAR(' ||(decode( length,0, > 1,\ > length))||') ',12,'DATE' ,23,'RAW( '||length| |')',24,' LONG > RAW',69,'ROWID' ,96,decode( charsetform, 2,'NCHAR( '||decode( bitand(property\ > ,8388608),8388608, decode(spare3, 0,1,spare3) ||')','BYTECOUNT =>'||decode( l\ > ength,0,1,length) ||')'),'CHAR( '||decode( bitand(property, 8388608), 8388608\ > ,decode(spare3, 0,1,spare3) ||' char)',decode( length,0, 1,length) ||' > byte)')),97, 'VARCHAR( '||(decode( length,0, 1,length) )|| > ')',105,'MLSLABEL' ,106,'MLSLABEL' ,112,decode( charsetform, 2,'NCLOB' ,'CLOB\ > '),113,'BLOB' ,114,'BFILE' ,115,decode( charsetform, 2,'NCFILE' ,'CFILE') > ,178\ > ,'TIME('||scale| |')',179, 'TIME('|| scale||') WITH TIME > ZONE',180,'TIMESTAM P('||scale| |')',181, 'TIMESTAMP( '||scale| |') WITH TIME > ZONE',231,'TIMESTAM P('||scale| |') WITH LOCAL TIME ZONE',182,'INTERVAL > YEAR('||NVL( precision# , 0)||') TO MONTH',183,' INTERVAL > DAY('||NVL(precisio n#, 0)||') TO > SECOND('||scale| |')',208, 'UROWID(' ||spare1| |')',100, 'BINARY_FLOAT' > ,101,'\ > BINARY_DOUBLE' ,'UNDEFINED' )||decode( null$,0,' ',' NOT > NULL')||decode( bitand(property, 32), 0, ',', ' HIDDEN,'),name, type#, > intcol#, null$, bitand(property, 32) from col$ where obj#=:1 and > (bitand(property, 32) = 0 or bitand(property, 4194304) <> 0) order by > col# > END OF STMT > PARSE #14:c=0,e=6870, p=0,cr=0, cu=0,mis= 1,r=0,dep= 1,og=4,tim= 7947716956 > BINDS #14: > bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=0001 > size=24 offset=0 > bfp=06cdc1d0 bln=22 avl=04 flg=05 > value=67876 > EXEC > #14:c=15625, e=19942,p= 0,cr=0,cu= 0,mis=1,r= 0,dep=1,og= 4,tim=7947761818 > FETCH #14:c=0,e=167, p=0,cr=3, cu=0,mis= 0,r=1,dep= 1,og=4,tim= 7947775488 > FETCH #14:c=0,e=27, p=0,cr=0, cu=0,mis= 0,r=1,dep= 1,og=4,tim= 7947778415 > FETCH #14:c=0,e=81, p=0,cr=0, cu=0,mis= 0,r=0,dep= 1,og=4,tim= 7947789044 > STAT #14 id=1 cnt=2 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=3 pr=0 pw=0 > time=186 us)' > STAT #14 id=2 cnt=2 pid=1 pos=1 obj=21 op='TABLE ACCESS CLUSTER COL$ > (cr=3 pr=0 pw=0 time=94 us)' > STAT #14 id=3 cnt=1 pid=2 pos=1 obj=3 op='INDEX UNIQUE SCAN I_OBJ# (cr=2 > pr=0 pw=0 time=39 us)' > ============ ========= > PARSING IN CURSOR #17 len=100 dep=0 uid=448 oct=47 lid=448 > tim=7947809722 hv=4257448168 ad='1bcc54c0' > begin > for i in 1 .. 1000 > loop > insert into abcd_test > values (i,'Nama_'|| i); > end loop; > commit; > end; > END OF STMT > PARSE > #17:c=109375, e=220998, p=0,cr=5, cu=0,mis= 1,r=0,dep= 0,og=1,tim= 7947809713 > BINDS #17: > ============ ========= > PARSING IN CURSOR #5 len=49 dep=1 uid=448 oct=2 lid=448 tim=7947871900 > hv=1820018842 ad='1b174298' > INSERT INTO ABCD_TEST VALUES (:B1 ,'Nama_'||:B1 ) > END OF STMT > PARSE #5:c=0,e=253, p=0,cr=0, cu=0,mis= 1,r=0,dep= 1,og=1,tim= 7947871891 > ============ ========= > PARSING IN CURSOR #3 len=175 dep=2 uid=0 oct=3 lid=0 tim=7947894101 > hv=1729330152 ad='1da6e6b8' > select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled from > obj$ o,user$ u,trigger$ t where t.baseobject= :1 and t.obj#=o.obj# and > o.owner#=u.user# order by o.obj# > END OF STMT > PARSE #3:c=0,e=445, p=0,cr=0, cu=0,mis= 1,r=0,dep= 2,og=1,tim= 7947894094 > BINDS #3: > bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=00 oacfl2=0001 > size=24 offset=0 > bfp=06cd702c bln=22 avl=04 flg=05 > value=67876 > EXEC #3:c=0,e=13358, p=0,cr=0, cu=0,mis= 1,r=0,dep= 2,og=4,tim= 7947933716 > FETCH #3:c=0,e=244, p=0,cr=1, cu=0,mis= 0,r=0,dep= 2,og=4,tim= 7947941493 > STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=1 pr=0 pw=0 > time=284 us)' > STAT #3 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=1 pr=0 pw=0 > time=235 us)' > STAT #3 id=3 cnt=0 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=1 pr=0 pw=0 > time=223 us)' > ============ ========= > PARSING IN CURSOR #2 len=116 dep=3 uid=0 oct=3 lid=0 tim=7947960182 > hv=854877822 ad='1d9db93c' > select > o.owner#,o.name, o.namespace, o.remoteowner, o.linkname, o.subname, o.dataobj\ > #,o.flags from obj$ o where o.obj#=:1 > END OF STMT > PARSE #2:c=0,e=611, p=0,cr=0, cu=0,mis= 1,r=0,dep= 3,og=4,tim= 7947960124 > BINDS #2: > bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=0001 > size=24 offset=0 > bfp=06cd6fd8 bln=22 avl=02 flg=05 > value=84 > EXEC > #2:c=31250,e= 17684,p=0, cr=0,cu=0, mis=1,r=0, dep=3,og= 4,tim=7947997447 > FETCH #2:c=0,e=74, p=0,cr=3, cu=0,mis= 0,r=1,dep= 3,og=4,tim= 7948014164 > STAT #3 id=4 cnt=0 pid=3 pos=1 obj=84 op='TABLE ACCESS BY INDEX ROWID > TRIGGER$ (cr=1 pr=0 pw=0 time=214 us)' > STAT #3 id=5 cnt=0 pid=4 pos=1 obj=128 op='INDEX RANGE SCAN I_TRIGGER1 > (cr=1 pr=0 pw=0 time=200 us)' > STAT #3 id=6 cnt=0 pid=3 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID > OBJ$ (cr=0 pr=0 pw=0 time=0 us)' > STAT #3 id=7 cnt=0 pid=6 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=0 > pr=0 pw=0 time=0 us)' > STAT #3 id=8 cnt=0 pid=2 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ > (cr=0 pr=0 pw=0 time=0 us)' > STAT #3 id=9 cnt=0 pid=8 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# > (cr=0 pr=0 pw=0 time=0 us)' > ============ ========= > PARSING IN CURSOR #12 len=210 dep=2 uid=0 oct=3 lid=0 tim=7948041534 > hv=864012087 ad='1d7c97b0' > select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, > sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, > spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 > END OF STMT > PARSE #12:c=0,e=912, p=0,cr=0, cu=0,mis= 1,r=0,dep= 2,og=3,tim= 7948041527 > BINDS #12: > bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=0001 > size=24 offset=0 > bfp=06cea4b4 bln=22 avl=04 flg=05 > value=67876 > bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=0001 > size=24 offset=0 > bfp=06cdc1bc bln=24 avl=02 flg=05 > value=2 > EXEC #12:c=0,e=4170, p=0,cr=0, cu=0,mis= 1,r=0,dep= 2,og=3,tim= 7948065288 > FETCH #12:c=0,e=49, p=0,cr=2, cu=0,mis= 0,r=0,dep= 2,og=3,tim= 7948065413 > BINDS #5: > bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=206001 > size=48 offset=0 > bfp=06c9fba0 bln=22 avl=02 flg=05 > value=1 > bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=206001 > size=0 offset=24 > bfp=06c9fbb8 bln=22 avl=02 flg=01 > value=1 > EXEC > #5:c=156250, e=174368, p=0,cr=7, cu=23,mis= 1,r=1,dep= 1,og=1,tim= 7948067659 > BINDS #5: [Non-text portions of this message have been removed]
