asnussual, sewaktu akan truncate table, bisa pasang event 10046?? 2009/3/27 Yulius Wibowo <[email protected]>: > Tora, > > Boleh tahu nggak ya, > "alat ukur" apa yg Tora gunakan utk mengukur waktu TRUNCATE & DELETE tsb? > Apakah menggunakan SET TIMING ON? atau ??? > > bw > > > --- In [email protected], ** Tora Fahrudin ** <tora_ifst...@...> > wrote: >> >> >> Makasih pencerahannya juga mas ujang. Tapi kira kira apa ya yang menyebabkan >> truncate pada data yang kecil malah lebih lambat. :) >> >> Thanks, >> >> Tora Fahrudin http://torafahrudin.wordpress.com >> >> (-- ^_^ --) >> >> >> --- On Tue, 3/24/09, Ujang Jaenudin <ujang.jaenu...@...> wrote: >> >> From: Ujang Jaenudin <ujang.jaenu...@...> >> Subject: Re: [indo-oracle] Re: Proses yang dijalankan ketika Truncate Data >> To: [email protected] >> Date: Tuesday, March 24, 2009, 9:09 PM >> >> >> >> >> >> >> >> >> >> >> >> >> create table t10 (a number, b number, c number) tablespace users; >> >> >> >> select extent_id,file_ id,block_ id,blocks from dba_extents where >> >> segment_name= 'T10'; >> >> >> >> insert into t10 values(1,1,1) ; >> >> 6x >> >> commit; >> >> >> >> alter system dump datafile 4 block min 953 block max 961; >> >> >> >> Block header dump: 0x010003bd >> >> Object id on Block? Y >> >> seg/obj: 0xf839 csc: 0x00.13b5f4 itc: 2 flg: E typ: 1 - DATA >> >> brn: 0 bdba: 0x10003b9 ver: 0x01 opc: 0 >> >> inc: 0 exflg: 0 >> >> >> >> Itl Xid Uba Flag Lck Scn/Fsc >> >> 0x01 0x000e.01d.0000024e 0x07000548.00c8. 1e --U- 6 fsc >> 0x0000.0013b672 >> >> 0x02 0x0000.000.00000000 0x00000000.0000. 00 ---- 0 fsc >> 0x0000.00000000 >> >> >> >> data_block_dump, data header at 0x7700a64 >> >> ============ === >> >> tsiz: 0x1f98 >> >> hsiz: 0x1e >> >> pbl: 0x07700a64 >> >> bdba: 0x010003bd >> >> 76543210 >> >> flag=------- - >> >> ntab=1 >> >> nrow=6 >> >> frre=-1 >> >> fsbo=0x1e >> >> fseo=0x1f50 >> >> avsp=0x1f32 >> >> tosp=0x1f32 >> >> 0xe:pti[0] nrow=6 offs=0 >> >> 0x12:pri[0] offs=0x1f8c >> >> 0x14:pri[1] offs=0x1f80 >> >> 0x16:pri[2] offs=0x1f74 >> >> 0x18:pri[3] offs=0x1f68 >> >> 0x1a:pri[4] offs=0x1f5c >> >> 0x1c:pri[5] offs=0x1f50 >> >> block_row_dump: >> >> tab 0, row 0, @0x1f8c >> >> tl: 12 fb: --H-FL-- lb: 0x1 cc: 3 >> >> col 0: [ 2] c1 02 >> >> col 1: [ 2] c1 02 >> >> col 2: [ 2] c1 02 >> >> tab 0, row 1, @0x1f80 >> >> tl: 12 fb: --H-FL-- lb: 0x1 cc: 3 >> >> col 0: [ 2] c1 02 >> >> col 1: [ 2] c1 02 >> >> col 2: [ 2] c1 02 >> >> tab 0, row 2, @0x1f74 >> >> tl: 12 fb: --H-FL-- lb: 0x1 cc: 3 >> >> col 0: [ 2] c1 02 >> >> col 1: [ 2] c1 02 >> >> col 2: [ 2] c1 02 >> >> tab 0, row 3, @0x1f68 >> >> tl: 12 fb: --H-FL-- lb: 0x1 cc: 3 >> >> col 0: [ 2] c1 02 >> >> col 1: [ 2] c1 02 >> >> col 2: [ 2] c1 02 >> >> tab 0, row 4, @0x1f5c >> >> tl: 12 fb: --H-FL-- lb: 0x1 cc: 3 >> >> col 0: [ 2] c1 02 >> >> col 1: [ 2] c1 02 >> >> col 2: [ 2] c1 02 >> >> tab 0, row 5, @0x1f50 >> >> tl: 12 fb: --H-FL-- lb: 0x1 cc: 3 >> >> col 0: [ 2] c1 02 >> >> col 1: [ 2] c1 02 >> >> col 2: [ 2] c1 02 >> >> end_of_block_ dump >> >> >> >> truncate table t10; >> >> >> >> alter system dump datafile 4 block min 953 block max 961; >> >> >> >> Block header dump: 0x010003bd >> >> Object id on Block? Y >> >> seg/obj: 0xf839 csc: 0x00.13b5f4 itc: 2 flg: E typ: 1 - DATA >> >> brn: 0 bdba: 0x10003b9 ver: 0x01 opc: 0 >> >> inc: 0 exflg: 0 >> >> >> >> Itl Xid Uba Flag Lck Scn/Fsc >> >> 0x01 0x000e.01d.0000024e 0x07000548.00c8. 1e --U- 6 fsc >> 0x0000.0013b672 >> >> 0x02 0x0000.000.00000000 0x00000000.0000. 00 ---- 0 fsc >> 0x0000.00000000 >> >> >> >> data_block_dump, data header at 0x7700a64 >> >> ============ === >> >> tsiz: 0x1f98 >> >> hsiz: 0x1e >> >> pbl: 0x07700a64 >> >> bdba: 0x010003bd >> >> 76543210 >> >> flag=------- - >> >> ntab=1 >> >> nrow=6 >> >> frre=-1 >> >> fsbo=0x1e >> >> fseo=0x1f50 >> >> avsp=0x1f32 >> >> tosp=0x1f32 >> >> 0xe:pti[0] nrow=6 offs=0 >> >> 0x12:pri[0] offs=0x1f8c >> >> 0x14:pri[1] offs=0x1f80 >> >> 0x16:pri[2] offs=0x1f74 >> >> 0x18:pri[3] offs=0x1f68 >> >> 0x1a:pri[4] offs=0x1f5c >> >> 0x1c:pri[5] offs=0x1f50 >> >> block_row_dump: >> >> tab 0, row 0, @0x1f8c >> >> tl: 12 fb: --H-FL-- lb: 0x1 cc: 3 >> >> col 0: [ 2] c1 02 >> >> col 1: [ 2] c1 02 >> >> col 2: [ 2] c1 02 >> >> tab 0, row 1, @0x1f80 >> >> tl: 12 fb: --H-FL-- lb: 0x1 cc: 3 >> >> col 0: [ 2] c1 02 >> >> col 1: [ 2] c1 02 >> >> col 2: [ 2] c1 02 >> >> tab 0, row 2, @0x1f74 >> >> tl: 12 fb: --H-FL-- lb: 0x1 cc: 3 >> >> col 0: [ 2] c1 02 >> >> col 1: [ 2] c1 02 >> >> col 2: [ 2] c1 02 >> >> tab 0, row 3, @0x1f68 >> >> tl: 12 fb: --H-FL-- lb: 0x1 cc: 3 >> >> col 0: [ 2] c1 02 >> >> col 1: [ 2] c1 02 >> >> col 2: [ 2] c1 02 >> >> tab 0, row 4, @0x1f5c >> >> tl: 12 fb: --H-FL-- lb: 0x1 cc: 3 >> >> col 0: [ 2] c1 02 >> >> col 1: [ 2] c1 02 >> >> col 2: [ 2] c1 02 >> >> tab 0, row 5, @0x1f50 >> >> tl: 12 fb: --H-FL-- lb: 0x1 cc: 3 >> >> col 0: [ 2] c1 02 >> >> col 1: [ 2] c1 02 >> >> col 2: [ 2] c1 02 >> >> end_of_block_ dump >> >> >> >> kalau diperhatikan, sebenarnya isi data (row data masih ada) cuman >> >> oracle hanya set flag pada header object/segment tsb (di block header >> >> sini antara block id 953-954 karena by default di BMB ini oracle >> >> allocate 2 block untuk headernya, sedangkan 1 block block id 955 mulai >> >> digunakan data, indikasinya dari " unformatted: 5 total: 8 >> >> first useful block: 3 "), supaya block2 yg pernah kepakai >> >> data bisa direplace. >> >> >> >> malah segment yg ditruncate ini bisa direcover dengan bantuan BBED >> >> asalkan belum keduluan oleh block cleanout (beberapa test case >> >> sepertinya jalan, not sure in the real world). >> >> >> >> 2009/3/25 ** Tora Fahrudin ** <tora_ifstt03@ yahoo.com>: >> >> > Ok makasih mas bowo, >> >> > >> >> > Tapi saya masih bingung kok semakin banyak data justru proses TRUNCATE >> > jadi lebih cepat, sedangkan semakin sedikit data malah semakin lambat. >> > Kenapa tidak linear dengan jumlah datanya. >> >> > >> >> > Makasih mas bowo. >> >> > >> >> > >> >> > Thanks, >> >> > >> >> > Tora Fahrudin http://torafahrudin .wordpress. com >> >> > >> >> > (-- ^_^ --) >> >> > >> >> > >> >> > --- On Tue, 3/24/09, Yulius Wibowo <yulius_wibowo@ yahoo.com> wrote: >> >> > >> >> > From: Yulius Wibowo <yulius_wibowo@ yahoo.com> >> >> > Subject: [indo-oracle] Re: Proses yang dijalankan ketika Truncate Data >> >> > To: indo-oracle@ yahoogroups. com >> >> > Date: Tuesday, March 24, 2009, 6:15 PM >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > Tora san, >> >> > >> >> > >> >> > >> >> > TRUNCATE: adalah perintah DDL (Data Definition Language). >> >> > >> >> > Yg diupdate adalah informasi dari tablenya di dalam data dictionary. >> >> > >> >> > - Jumlah extent di reset ke minimum extent. >> >> > >> >> > - High Water Mark (HWM) di reset ke bagian depan/awal. Sehingga data yg >> > ada di atas HWM dianggap tidak ada/kosong. >> >> > >> >> > - Semua bekas extent akan di-release, dan bisa dipakai ulang oleh segment >> > ybs atau segment yg lain. >> >> > >> >> > >> >> > >> >> > DELETE: adalah perintah DML (Data Manipulation Language) >> >> > >> >> > - Jumlah extent tetap. >> >> > >> >> > - HWM tidak berubah >> >> > >> >> > - Yg dihapus adalah record2nya (sesuai dgn kriteria/WHERE clause-nya) >> >> > >> >> > >> >> > >> >> > Analognya sama dengan kalau kita memformat Floppy Disk: >> >> > >> >> > - Format biasa = DELETE >> >> > >> >> > ---> Setiap sector dari FD akan dihapus satu persatu >> >> > >> >> > ---> butuh waktu lama sesuai dengan jumlah sektor >> >> > >> >> > >> >> > >> >> > - Quick format = TRUNCATE >> >> > >> >> > ---> Yg dihapus hanya FAT-nya saja >> >> > >> >> > ---> butuh waktu cepat >> >> > >> >> > >> >> > >> >> > b...@jp >> >> > >> >> > >> >> > >> >> > --- In indo-oracle@ yahoogroups. com, ** Tora Fahrudin ** <tora_ifstt03@ >> > ...> wrote: >> >> > >> >> >> >> >> > >> >> >> >> >> > >> >> >> Dear all, >> >> > >> >> >> >> >> > >> >> >> Salam untuk teman teman semua, maaf gak pernah nongol kok tiba tiba nanya >> >> :D >> >> > >> >> >> >> >> > >> >> >> Begini, ada rekan yang tau tidak bagaimana proses Truncate pada sebuah >> >> tabel itu? >> >> > >> >> >> >> >> > >> >> >> Saya agak heran dengan perlakuan truncate pada tabel yang sama dengan isi >> >> data 5 baris, 10 baris, 20 baris, 50 baris, 100 baris. >> >> > >> >> >> >> >> > >> >> >> Yang mengejutkan adalah waktu / response time dari perintah TRUNCATE >> >> tersebut menunjukkan bahwa trendnya justru tidak sebanding dengan jumlah >> >> data. Justru semakin kecil baris data yang ada, waktu TRUNCATE malah >> >> lebih lama. >> >> > >> >> >> >> >> > >> >> >> Kira kira apa ya penyebabnya. Percobaan sudah di coba berkali kali, >> >> bahkan skenario di rubah yaitu jumlah baris 100 d TRUNCATE. 50 baris di >> >> TRUNCATE dst tetap menghasilkan response time yang sama -> TRUNCATE lebih >> >> lama jika jumlah data semakin sedikit. >> >> > >> >> >> >> >> > >> >> >> Mohon bantuan rekan rekan semua. Terima kasih ^_^ >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> [Non-text portions of this message have been removed] >> > > > > > ------------------------------------ > > -- > -----------I.N.D.O - O.R.A.C.L.E--------------- > Keluar: [email protected] > Website: http://indooracle.wordpress.com > http://www.facebook.com/group.php?gid=51973053515 > ----------------------------------------------- > > Bergabung dengan Indonesia Thin Client User Groups, > Terminal Server, Citrix, New Moon Caneveral, di: > http://indo-thin.blogspot.comYahoo! Groups Links > > > >
-- thanks and regards ujang | oracle dba | mysql dba http://ora62.wordpress.com
