----
Compare the performance benchmark for row-by-row update and single SQL set based update.
The benchmark matrix supply supported information to Product Manager to prove why we should move some
row-by-row "real time"(that can be near real time) processes to Asynchronous set based bulk process.
file://T:/NextAction/benchmark_update_listing.sql
Solution
--------
Generate testing data, update some rows, benchmark with RunStats package.
Result
------
From benchmark, you can see
- Set based process vs. row-by-row process, commit out the loop.
Row-by-row process takes 3 times longer Elapsed Time, 3 times more CPU, 3 times more logical I/O,
and use 258% more Latches, latches are a type of lock, locks are serialization devices,
serialization devices inhibit scalability.
- Set based process vs. row-by-row process, commit in the loop.
Row-by-row process takes 5 times longer Elapsed Time, 5 times more CPU, 5 times more logical I/O,
and use 1,019.26% more Latches.
- If we introduce blocking row-by-row process from OIMS, by introducing more latches from different concurrent users,
it will be even worse.
A latch is a serialization device, the more people that request the same latch at the some time necessarily implies
"longer runtimes" - as the number of concurrent users go up, so will the observed run times and cpu times
(as you spin on the latch trying to get it), as we experience contention.
So,
SQL SET BASED
You did good by turning slow by slow into BULK.
You could have done better by turning BULK into SET OPERATION.
Stats
-----
*) commit out the loop
Run1 ran in 79 hsecs
Run2 ran in 25 hsecs
run 1 ran in 316% of the time
Name Run1 Run2 Diff
STAT...db block changes 4,555 4,568 13
STAT...db block gets 2,418 2,444 26
STAT...Elapsed Time 80 26 -54
STAT...CPU used by this session 81 26 -55
LATCH.simulator hash latch 308 181 -127
STAT...table scan rows gotten 2,728 2,222 -506
STAT...redo size 5,841,592 5,842,304 712
LATCH.checkpoint queue latch 2,223 102 -2,121
STAT...execute count 2,224 4 -2,220
STAT...sorts (memory) 2,222 0 -2,222
LATCH.MinActiveScn Latch 2,428 198 -2,230
LATCH.object queue header operation 2,862 393 -2,469
STAT...undo change vector size 2,837,256 2,839,872 2,616
LATCH.cache buffers lru chain 4,445 195 -4,250
STAT...session logical reads 9,292 2,751 -6,541
STAT...consistent gets 6,874 307 -6,567
LATCH.cache buffers chains 23,525 12,415 -11,110
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
36,466 14,118 -22,348 258.29%
PL/SQL procedure successfully completed.
*) commit in the loop
Run1 ran in 129 hsecs
Run2 ran in 24 hsecs
run 1 ran in 537.5% of the time
Name Run1 Run2 Diff
STAT...CPU used by this session 130 26 -104
STAT...Elapsed Time 132 25 -107
LATCH.Consistent RBA 1,260 8 -1,252
STAT...commit cleanouts 2,224 100 -2,124
LATCH.redo writing 3,782 25 -3,757
LATCH.cache buffers lru chain 4,454 195 -4,259
STAT...db block changes 8,916 4,572 -4,344
LATCH.session allocation 4,481 52 -4,429
STAT...db block gets 8,917 2,456 -6,461
STAT...consistent gets 6,882 408 -6,474
STAT...recursive calls 6,714 35 -6,679
LATCH.redo allocation 8,225 29 -8,196
LATCH.In memory undo latch 8,893 11 -8,882
LATCH.DML lock allocation 8,891 7 -8,884
LATCH.undo global data 11,136 129 -11,007
STAT...session logical reads 15,799 2,864 -12,935
LATCH.enqueue hash chains 13,351 25 -13,326
LATCH.cache buffers chains 60,632 12,552 -48,080
STAT...undo change vector size 2,989,432 2,841,148 -148,284
STAT...redo size 6,391,412 5,844,136 -547,276
STAT...IMU undo allocation size 4,193,320 1,080 -4,192,240
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
145,815 14,306 -131,509 1,019.26%
PL/SQL procedure successfully completed.
Setup
-----
drop table listing_wide purge;
create table listing_wide
(
vendor_id number(10,0) Not Null ,
listing_id number(20,0) Not Null ,
vendor_listing_id Varchar(40) Not Null ,
hash_code VarChar2(32) Not Null ,
qty number(5,0) Not Null ,
price number(10,2) Not Null ,
currency varchar2(3) default 'USD' Not Null,
listingtype number(4,0) default 1001 Not Null,
book_lang varchar2(3) default ' ' Not Null ,
create_date Date default sysdate Not Null ,
update_date Date default sysdate Not Null ,
abe_cat0 varchar2(100) ,
abe_cat1 varchar2(100) ,
abe_cat2 varchar2(100) ,
vend_bsa Varchar2(30) ,
abe_bsa Varchar2(30) ,
binding varchar2(30) ,
booktype varchar2(30) ,
bookcondition varchar2(30) ,
cat_providerid Number(5,0) ,
cat_providerkey Varchar2(50) ,
edition_num number(10) ,
edition_txt varchar2(40) ,
editor varchar(255) ,
illustrator varchar2(255) ,
inscription varchar2(50) ,
isbn_raw Varchar2(25) ,
isbn_vendor13 Number(13,0) ,
isbn_matched13 Number(13,0) ,
jacketcondition Varchar2(30) ,
pictures Varchar2(255) ,
printing Varchar2(20) ,
publisher_name varchar2(750) ,
publisher_year Number(4) ,
publisher_location varchar2(750) ,
item_size Varchar2(50) ,
subject Varchar2(2000) ,
synopsis Varchar2(255) ,
author varchar(750) ,
title varchar2(750) ,
vend_cat varchar2(255) ,
volume Varchar2(50) ,
weight Varchar2(40) ,
description varchar2(4000)
--, constraint listing_wide_pk primary key(listing_id)
)
-- TABLESPACE data_auto
NOLOGGING
PARTITION BY LIST (vendor_id)
(
PARTITION v2134736 values( 2134736 ),
PARTITION v8408184 values( 8408184 ),
PARTITION v999 values( 999 ),
PARTITION vendor_NULL VALUES (NULL),
PARTITION vendor_small VALUES (DEFAULT)
)
NOCOMPRESS
NOPARALLEL
ENABLE ROW MOVEMENT;
create index listing_wide_i_hs on listing_wide(vendor_listing_id, hash_code) local nologging;
create index listing_wide_i_id on listing_wide(listing_id) local nologging;
truncate table listing_wide;
insert /*+ append */ into listing_wide
(listing_id, vendor_id, price, qty, hash_code, vendor_listing_id, author, title,description,isbn_raw, vend_bsa)
select
rownum listing_id,
mod(rownum,8) vendor_id,
10.55 price,
30 qty,
rpad('x',31,'x') hash_code,
rownum vendor_listing_id,
rpad(rownum,400,'x') author,
rpad('x',400,'x') title,
rpad('x',300,'x') description,
rownum isbn_raw,
mod(rownum,100)||','||mod(Round(rownum/10),100) bsa
from dual
connect by level <= 100100;
commit;
select vendor_id, isbn_raw, hash_code, author
from listing_wide where rownum <= 3;
exec dbms_stats.gather_table_stats(user,'listing_wide');
drop table listing_file;
create table listing_file
PARTITION BY LIST (time_part_key)
SUBPARTITION BY LIST (match_col)
SUBPARTITION TEMPLATE
(SUBPARTITION vendor_id_part VALUES (1) ,
SUBPARTITION hash_code_part VALUES (2) ,
SUBPARTITION listing_id_part VALUES (3) ,
SUBPARTITION other_part VALUES (DEFAULT)
)
(
PARTITION data_part_0 VALUES (0),
PARTITION data_part_1 VALUES (1),
PARTITION data_part_2 VALUES (2),
PARTITION data_part_3 VALUES (3),
PARTITION data_part_4 VALUES (4),
PARTITION data_part_5 VALUES (5),
--PARTITION dummy_null VALUES (NULL),
PARTITION dummy_other VALUES (DEFAULT)
)
--tablespace data_auto
nologging
as
select a.*
--, cast(null as timestamp(3)) update_date
, cast(null as number(2)) time_part_key
, 9 dest_queue
, 9 match_col
from listing_wide a
where 1=0;
alter table listing_file modify (
currency varchar2(3) default 'USD' ,
listingtype number(4,0) default 1001 ,
book_lang varchar2(3) default ' ' ,
create_date Date default sysdate ,
update_date Date default sysdate
);
alter table listing_file modify
(
update_date default systimestamp,
time_part_key default mod(floor(to_number(to_char(sysdate,'mi'))/10),10)
);
truncate table listing_file;
insert /*+ append */ into listing_file
(listing_id, vendor_id, price, qty, hash_code, vendor_listing_id, author, title,description,isbn_raw, vend_bsa
, match_col)
select
rownum listing_id,
mod(rownum,8) vendor_id,
10.55 price,
30 qty,
rpad('x',31,'x') hash_code,
rownum vendor_listing_id,
rpad(rownum,400,'x') author,
rpad('x',400,'x') title,
rpad('x',300,'x') description,
rownum isbn_raw,
mod(rownum,100)||','||mod(Round(rownum/10),100) bsa,
3 match_col
from dual
connect by level <= 2222;
commit;
exec dbms_stats.gather_table_stats(user,'listing_file');
Test
----
set linesize 120
set serveroutput on
Begin
runstats_pkg.rs_start;
for s in (select * from listing_file)
loop
update listing_wide d
set
d.price = s.price ,
d.qty = s.qty ,
d.hash_code = s.hash_code ,
d.vendor_listing_id = s.vendor_listing_id,
d.author = s.author ,
d.title = s.title ,
d.description = s.description ,
d.isbn_raw = s.isbn_raw ,
d.vend_bsa = s.vend_bsa
where s.vendor_id = d.vendor_id and s.listing_id = d.listing_id
;
--commit;
end loop;
commit;
runstats_pkg.rs_middle;
MERGE INTO listing_wide d
using listing_file s
on (s.vendor_id = d.vendor_id and s.listing_id = d.listing_id)
when matched then
update set
d.price = s.price ,
d.qty = s.qty ,
d.hash_code = s.hash_code ,
d.vendor_listing_id = s.vendor_listing_id,
d.author = s.author ,
d.title = s.title ,
d.description = s.description ,
d.isbn_raw = s.isbn_raw ,
d.vend_bsa = s.vend_bsa
;
commit;
runstats_pkg.rs_stop(5);
End;
/
Reference---------
http://tkyte.blogspot.com/2006/10/slow-by-slow.html
- bulking it up with BULK COLLECT, bulk-update them back into the database, using the FORALL statement with the UPDATE
http://www.oracle.com/technetwork/issue-archive/2011/11-may/o31asktom-354139.html
No comments:
Post a Comment