Pak Ujang, Response saya di bawah itu menanyakan tentang membandingkan antara Individual Update Composite dan Composite. Tidak lagi antara Update di gabung dengan Individual Update.
*" > Maaf, mungkin pertanyaan saya kurang jelas. > Sebenarnya saya mempertanyakan kasus Individual Update (jadi maintain index > tidak berbarengan) untuk composite (cuman 1 index, 3 column tersebut dalam 1 > block) bisa *lebih lama* dari non-composite (3 index, more data block = more > IO)"* Mengenai perbandingan Update sekaligus Vs Individual Update anda sudah bilang bahwa hasilnya-nya tergantung environmentnya (Disk setup, parallel processing configuration, etc). I agree with that. Semoga lebih jelas sekarang :) 2008/10/17 Ujang Jaenudin <[EMAIL PROTECTED]> > pada prinsipnya mengupdate "index key" oracle akan melakukan (di segment > index): > - delete index key entry lama > - insert index key baru > - ini bisa dibuktikan dgn block dump > > di lab berikut ini, saya coba yg kasus t2 (non-composite, dimana masih > tetep lama pengerjaannya) > yg menarik adalah, pada saat index berjalan saya cek v$process, > ternyata ada process tambahan: > > 2956 ORACLE.EXE (C001) ==> parallel coordinator > 476 ORACLE.EXE (P000) ==> slave1 > 3700 ORACLE.EXE (P001) ==> slave2 > 156 ORACLE.EXE (P002) ==> slave3 > > kenapa harus 3 slave? > saya berasumsi karena jumlah index key (table t2 adalah non composite) > nya 3 index. > > jadi walaupun dgn 1 line command update, karena mengupdate 3 index > key, maka si oracle melakukan > maintain index dgn parallel, disini jelas, disk anda harus > properly designed untuk melayani parallel server. > kalo toh dilihat dari sisi SGA, whatever cache tidak relevan :) -- just my > $0.02 > > dan perlu diingat pekerjaan update index keys secara design aplikasi > adalah DODOL :) > tidak mungkin dilakukan pada saat transactional.... > past pekerjaan yg bersifat batch. > > even parallel server disini tidak diset :) > > SQL> show parameter parallel; > > NAME TYPE VALUE > ------------------------------------ ----------- --------- > fast_start_parallel_rollback string LOW > parallel_adaptive_multi_user boolean TRUE > parallel_automatic_tuning boolean FALSE > parallel_execution_message_size integer 2148 > parallel_instance_group string > parallel_max_servers integer 40 > parallel_min_percent integer 0 > parallel_min_servers integer 0 > parallel_server boolean FALSE > parallel_server_instances integer 1 > parallel_threads_per_cpu integer 2 > recovery_parallelism integer 0 > > set feedback on > set timing on; > > SQL> select count(*) from v$process; > > COUNT(*) > ---------- > 23 > > > SQL> update t2 set prod_id=0,cust_id=0,channel_id=0; > > SQL> select count(*) from v$process; > COUNT(*) > ---------- > 27 > .... > .... > .... > > 918843 rows updated. > Elapsed: 00:06:37.07 > > SQL> update t2 set prod_id=1,cust_id=1,channel_id=1; > 918843 rows updated. > Elapsed: 00:03:40.75 > > SQL> update t2 set prod_id=1,cust_id=1,channel_id=1; > 918843 rows updated. > Elapsed: 00:00:23.40 > > SQL> update t2 set prod_id=3,cust_id=2,channel_id=3; > 918843 rows updated. > Elapsed: 00:04:06.93 > > SQL> alter session set events='10046 trace name context forever, level 8'; > Session altered. > Elapsed: 00:00:00.01 > > SQL> update t2 set prod_id=9,cust_id=4,channel_id=5; > > SQL> select count(*) from v$process; > COUNT(*) > ---------- > 25 > .... > .... > .... > > 918843 rows updated. > Elapsed: 00:05:14.54 > > SQL> select count(*) from v$process; > > COUNT(*) > ---------- > 23 > > -- > thanks and regards > ujang | oracle dba > jakarta | http://ora62.wordpress.com > > 2008/10/17 Yoel Susanto <[EMAIL PROTECTED] <yoel.susanto%40gmail.com> > >: > > *"sebenarnya testing tsb tidak mencerminkan applicable untuk semua > > > > > environment....maklum lah namanya juga wind blowz dengan hardisknya > > cuman 1 biji, IDE pula :)" > > * > > yup, you've said it :) > > > > *"karena sewaktu issue update tsb oracle harus maintain 3 index > > berbarengan, jika physical disk cuman satu seperti di mesin saya, ya > > wajar toh bakal collision di disk..."* > > > > Maaf, mungkin pertanyaan saya kurang jelas. > > Sebenarnya saya mempertanyakan kasus Individual Update (jadi maintain > index > > tidak berbarengan) untuk composite (cuman 1 index, 3 column tersebut > dalam 1 > > block) bisa *lebih lama* dari non-composite (3 index, more data block = > more > > IO) > > > > Perhatikan timing Individual Update anda untuk non-composite berkisar > antara > > *Elapsed: 00:01:24.75* dan untuk Composite berkisar antara *00:01:49.15* > > > > *"SQL> select segment_name,blocks,bytes from dba_segments where > > segment_name like '%IDX%' and owner='SH'; > > > > SEGMENT_NAME BLOCKS BYTES > > -------------------- ---------- ---------- > > T1_IDX_CMP 20480 167772160 -- Composite > > T2_IDX_TIME 8192 67108864 -- Non Composite > > T2_IDX_CUST 9216 75497472 -- Non Composite > > T2_IDX_CHANNEL ** 8064 **66060288" -- Non Composite > > "* > > Seperti bisa di liat, total Bytes Non Composite - Bytes Composite = > 40894464 > > Bytes= 39 MB lebih banyak non-composite (4992 blocks more) > > > > Setelah saya liat lagi apa yang Anda lakukan. > > Seharusnya untuk experiment Individual Update, anda jangan flush buffer > > cachenya setiap kali anda update 1 column. > > Karena khan 3 update tersebut logika-nya adalah 1 transaksi. > > > > Bisa gak anda lakukan lagi individual update untuk composite dan > > non-composite? > > Dan kita liat hasilnya. kalo gak keberatan :) > > Seharusnya tanpa buffer flush lebih cepet yang composite, karena sebagian > > block udah di memory. > > > > 2008/10/17 Ujang Jaenudin <[EMAIL PROTECTED]<ujang.jaenudin%40gmail.com> > > > > > >> dear yoel, > >> > >> sebenarnya testing tsb tidak mencerminkan applicable untuk semua > >> environment....maklum lah namanya juga wind blowz dengan hardisknya > >> cuman 1 biji, IDE pula :) > >> > >> sebenarnya bisa saja update table yg statement pertama bisa lebih > >> kencang untuk kasus index-index pada table t2, asalkan index tsb > >> berada di physical disk yg berbeda. > >> no matter how much blocks it's occupied :) > >> tapi berapa ter-spreadnya block tsb di physical disk berbeda. > >> > >> karena sewaktu issue update tsb oracle harus maintain 3 index > >> berbarengan, jika physical disk cuman satu seperti di mesin saya, ya > >> wajar toh bakal collision di disk... > >> > >> FYI, > >> SGA di mesin ini terbatas, cuman 400MB, sga_target=384MB > >> > >> SQL> select segment_name,blocks,bytes from dba_segments where > >> segment_name in ('T1','T2') and owner='SH'; > >> > >> SEGMENT_NAME BLOCKS BYTES > >> -------------------- ---------- ---------- > >> T1 4608 37748736 > >> T2 4608 37748736 > >> > >> SQL> select segment_name,blocks,bytes from dba_segments where > >> segment_name like '%IDX%' and owner='SH'; > >> > >> SEGMENT_NAME BLOCKS BYTES > >> -------------------- ---------- ---------- > >> T1_IDX_CMP 20480 167772160 > >> T2_IDX_TIME 8192 67108864 > >> T2_IDX_CUST 9216 75497472 > >> T2_IDX_CHANNEL 8064 66060288 > >> > >> dari angka2 tsb, mustinya SGA sudah bisa keep, tapi testing sengaja > >> saya abaikan SGA dgn flush buffer_cache. > >> > >> just my $0.02 opinion. > >> > >> btw, pak yoenoes, kok yg bersangkutan gak undang2 ?? > >> > >> > >> -- > >> thanks and regards > >> ujang | oracle dba > >> jakarta | http://ora62.wordpress.com > >> > >> 2008/10/16 Yoel Susanto <[EMAIL > >> PROTECTED]<yoel.susanto%40gmail.com><yoel.susanto% > 40gmail.com> > >> >: > >> > >> > Pak Ujang, > >> > > >> > menarik nih.... > >> > > >> > "so, lama tidaknya tergantung composite or non composite :)" > >> > > >> > bisa gak kasih tau kenapa hal ini bisa berbeda? > >> > > >> > 1. composite --> 1 index 3 column (3 column ini dalam 1 blok) -- less > >> data > >> > block to process > >> > 2. non-composite --> 3 index masing2 1 column (1 column dalam 1 block) > >> > -- > >> > more data block to process > >> > (karena 3 index/segment blok datanya gak bisa > >> > campur) > >> > > >> > Jadi data blocknya antara composite dan non-composite adalah 1:3. > >> > > >> > kenapa yang lebih banyak data block bisa lebih cepat dari less data > >> block. > >> > > >> > 2008/10/15 Ujang Jaenudin > >> > <[EMAIL PROTECTED] <ujang.jaenudin%40gmail.com> > <ujang.jaenudin%40gmail.com> > > >> > > >> > > >> >> iseng2 ditesting di wind blowz..... kali aja dapet hadiah dari pak > >> >> yoenoes :) > >> >> > >> >> kesimpulan: > >> >> update table t1 > >> >> pada saat composite index, either semua kolom index diupdate pada 1 > >> >> eksekusi atau multiple eksekusi, timingnya gak jauh2 amat :) > >> >> > >> >> update table t2 > >> >> pada saat non composite index, jika diupdate kolom berindex dalam 1 > >> >> eksekusi akan memakan waktu disini sekitar 5 menit :) > >> >> tapi jika update masing2 kolom index tsb waktunya cuman 1:30 saja, > >> >> jika dijumlahkan sekitar 4:30 saja :) > >> >> > >> >> so, lama tidaknya tergantung composite or non composite :) > >> >> > >> >> C:\Documents and Settings\mild>sqlplus sys/oracle as sysdba > >> >> SQL*Plus: Release 10.2.0.4.0 - Production on Wed Oct 15 17:37:46 2008 > >> >> Copyright (c) 1982, 2007, Oracle. All Rights Reserved. > >> >> > >> >> Connected to: > >> >> Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - > Production > >> >> With the Partitioning, OLAP, Data Mining and Real Application Testing > >> >> options > >> >> > >> >> SQL> archive log list; > >> >> Database log mode No Archive Mode > >> >> Automatic archival Disabled > >> >> Archive destination D:\oracle\ora102\RDBMS > >> >> Oldest online log sequence 169 > >> >> Current log sequence 171 > >> >> > >> >> SQL> select count(*) from sales; > >> >> COUNT(*) > >> >> ---------- > >> >> 918843 > >> >> > >> >> SQL> set timing on; > >> >> SQL> select count(*) from sales; > >> >> COUNT(*) > >> >> ---------- > >> >> 918843 > >> >> > >> >> Elapsed: 00:00:00.00 > >> >> > >> >> SQL> desc sales; > >> >> Name Null? Type > >> >> ----------------------------------------- -------- --------------- > >> >> PROD_ID NOT NULL NUMBER > >> >> CUST_ID NOT NULL NUMBER > >> >> TIME_ID NOT NULL DATE > >> >> CHANNEL_ID NOT NULL NUMBER > >> >> PROMO_ID NOT NULL NUMBER > >> >> QUANTITY_SOLD NOT NULL NUMBER(10,2) > >> >> AMOUNT_SOLD NOT NULL NUMBER(10,2) > >> >> > >> >> SQL> create table t1 as select * from sales; > >> >> Table created. > >> >> Elapsed: 00:00:05.65 > >> >> > >> >> SQL> create table t2 as select * from t1; > >> >> Table created. > >> >> Elapsed: 00:00:02.86 > >> >> > >> >> SQL> create index t1_idx_cmp on t1(prod_id,cust_id,channel_id); > >> >> Index created. > >> >> Elapsed: 00:00:05.57 > >> >> > >> >> SQL> create index t2_idx_time on t2(prod_id); > >> >> Index created. > >> >> Elapsed: 00:00:01.92 > >> >> > >> >> SQL> create index t2_idx_cust on t2(cust_id); > >> >> Index created. > >> >> Elapsed: 00:00:05.31 > >> >> > >> >> SQL> create index t2_idx_channel on t2(channel_id); > >> >> Index created. > >> >> Elapsed: 00:00:01.64 > >> >> > >> >> SQL> alter system flush buffer_cache; > >> >> System altered. > >> >> Elapsed: 00:00:00.01 > >> >> > >> >> t1 composite > >> >> ----------------- > >> >> > >> >> SQL> update t1 set prod_id=0,cust_id=0,channel_id=0; > >> >> 918843 rows updated. > >> >> Elapsed: 00:01:30.40 > >> >> > >> >> SQL> alter system flush buffer_cache; > >> >> > >> >> SQL> update t1 set prod_id=1; > >> >> 918843 rows updated. > >> >> Elapsed: 00:01:26.09 > >> >> > >> >> SQL> alter system flush buffer_cache; > >> >> > >> >> SQL> update t1 set cust_id=1; > >> >> 918843 rows updated. > >> >> Elapsed: 00:01:47.57 > >> >> > >> >> SQL> alter system flush buffer_cache; > >> >> > >> >> SQL> update t1 set channel_id=1; > >> >> 918843 rows updated. > >> >> Elapsed: 00:01:49.15 > >> >> > >> >> SQL> alter system flush buffer_cache; > >> >> > >> >> t2 non composite > >> >> ----------------- > >> >> > >> >> SQL> update t2 set prod_id=0,cust_id=0,channel_id=0; > >> >> 918843 rows updated. > >> >> Elapsed: 00:04:56.23 > >> >> > >> >> SQL> alter system flush buffer_cache; > >> >> > >> >> SQL> update t2 set prod_id=1; > >> >> 918843 rows updated. > >> >> Elapsed: 00:01:24.75 > >> >> > >> >> SQL> alter system flush buffer_cache; > >> >> > >> >> SQL> update t2 set cust_id=1; > >> >> 918843 rows updated. > >> >> Elapsed: 00:01:29.17 > >> >> > >> >> SQL> update t2 set channel_id=1; > >> >> 918843 rows updated. > >> >> Elapsed: 00:01:29.31 > >> >> > >> >> > >> >> -- > >> >> thanks and regards > >> >> ujang | oracle dba > >> >> jakarta | http://ora62.wordpress.com > >> >> > >> >> 2008/10/15 Yoel Susanto > >> >> <[EMAIL PROTECTED] <yoel.susanto%40gmail.com><yoel.susanto% > 40gmail.com><yoel.susanto% > > >> 40gmail.com> > >> >> >: > >> >> > Menurut pendapat saya yah tetep 1 SQL lebih cepat dari lebih dari 2 > >> atau > >> >> > lebih SQL with or without index. > >> >> > > >> >> > alasan: > >> >> > 1. berapa kali oracle harus parse dan execute command > >> >> > 2. brapa kali oracle harus re-visit the blok > >> >> > 1 blok x 5 statement dibanding 1 blok x 1 statement > >> >> > 3. brapa jumlah undo & redo generated > >> >> > alasan sama dengan #2 > >> >> > 4. tergantung how big data & redo & undo generated > >> >> > berapa kali log switch, checkpoint dsb > >> >> > > >> >> > 1 sql tetep paleng murah. > >> >> > > >> >> > 2008/10/15 Ujang Jaenudin > >> >> > <[EMAIL PROTECTED] <ujang.jaenudin%40gmail.com><ujang.jaenudin% > 40gmail.com> > >> <ujang.jaenudin%40gmail.com> > >> >> > > >> >> > > >> >> >> hallo juragan, > >> >> >> > >> >> >> kalo indexnya composite dari kolom2 col_g sampai col_k seharusnya > yg > >> >> >> pertama lebih cepat. karena sekali saja oracle memaintain index. > >> >> >> > >> >> >> tapi jika indexnya tidak komposite (masing2 kolom diindex) > >> >> >> statement berikutnya lebih kenceng, karena memaintain banyak index > >> >> >> sekaligus pada saat issue 1 line update maka akan lebih lambat... > >> >> >> > >> >> >> CMIIW :) > >> >> >> > >> >> >> -- > >> >> >> thanks and regards > >> >> >> ujang | oracle dba > >> >> >> jakarta | http://ora62.wordpress.com > >> >> >> > >> >> >> 2008/10/10 Yoenoes Lucas > >> >> >> <[EMAIL PROTECTED] <yoenoes.lucas%40gmail.com><yoenoes.lucas% > 40gmail.com> <yoenoes.lucas% > >> 40gmail.com><yoenoes.lucas% > >> >> 40gmail.com> > >> >> >> >: > >> >> >> > >> >> >> > Dear gurus, > >> >> >> > > >> >> >> > asumsi suatu table dengan jumlah record 2 juta record, dan > >> mempunyai > >> >> >> > 3 > >> >> >> > index biasa (bukan unique) > >> >> >> > > >> >> >> > saya mau update table tersebut dengan perintah sql sbb : > >> >> >> > > >> >> >> > update table_x set col_g = 0, col_h =0, col_i = 0, cold_j = 0, > >> cold_k > >> >> = > >> >> >> 0; > >> >> >> > > >> >> >> > dibandingkan dengan > >> >> >> > > >> >> >> > update table_x set col_g = 0; > >> >> >> > update table_x set col_h = 0; > >> >> >> > update table_x set col_i = 0; > >> >> >> > update table_x set col_j = 0; > >> >> >> > update table_x set col_k = 0; > >> >> >> > > >> >> >> > mana yang lebih cepat perintah update yang di atas atau yang di > >> >> >> > bawah? > >> >> >> > serta alasannya kenapa lebih cepat yah.. > >> >> >> > > >> >> >> > thx > >> >> >> > Yoenoes > >> >> >> > >> > > -- Cheers, Yoel Susanto www.indo-oracletech.com [Non-text portions of this message have been removed]
