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]>:
> *"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]>
>
>> 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>
>> >:
>>
>> > 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>
>> >
>> >
>> >> 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>
>> >> >:
>> >> > 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>
>> >> >
>> >> >
>> >> >> 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>
>> >> >> >:
>> >> >>
>> >> >> > 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
>> >> >>
>>
