----
Compare the performance of single INSERT vs array BULK INSERT.
Result
------
After you run benchmark a few more times, you will get about same result as I saw.
Bulk DML spend 20% more CPU time,
Latches are about same. No big difference.
db block gets, physical reads, consistent gets, session logical reads are all about same.
Bulk DML take 2MB more session pga memory is expected, to cache association array (1200 bytes * 1000 rows) in shared_pool memory. no big deal.
Conclusion
-----------
Consider single SQL first.
Only when the data process logic is too complicated, we may go with Bulk DML, there will be a little performance impact, e.g. 20% more CPU used.
Notes: Thanks for Tom Kyte pointing out the design strategy.
(http://mujiang.blogspot.com/2011/06/question-to-asktom-in-oracle-magazine.html)
Reference
---------
Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)
12 Tuning PL/SQL Applications for Performance
Reducing Loop Overhead for DML Statements and Queries with Bulk SQL.
* Running One DML Statement Multiple Times (FORALL Statement)
* Retrieving Query Results into Collections (BULK COLLECT Clause)
Setup
-----
drop table listing_wide purge;
create table listing_wide
(
vendor_id number(10,0) Not Null ,
listing_id number(20,0) Not Null ,
match_col varchar2(1) Not Null ,
vendor_listing_id VarChar2(40) Not Null ,
hash_lite varchar2(32) ,
hash_code VarChar2(32) ,
qty number(5,0) Not Null ,
price number(10,2) Not Null ,
create_date Date default sysdate Not Null ,
update_date Date default sysdate Not Null ,
bsa_vend Varchar2(30) ,
isbn_raw Varchar2(25) ,
subject Varchar2(2000) ,
author varchar2(750) ,
title varchar2(750) ,
vend_catalog varchar2(255) ,
description varchar2(4000)
)
NOLOGGING
PARTITION BY LIST (vendor_id)
(
PARTITION v2134736 values( 2134736 ),
PARTITION v8408184 values( 8408184 ),
PARTITION v1 values( 1 ),
PARTITION v2 values( 2 ),
PARTITION vendor_NULL VALUES (NULL),
PARTITION vendor_small VALUES (DEFAULT)
)
NOCOMPRESS
NOPARALLEL
ENABLE ROW MOVEMENT;
drop table listing_purge;
create table listing_purge
NOLOGGING
PARTITION BY LIST (vendor_id)
(
PARTITION v2134736 values( 2134736 ),
PARTITION v8408184 values( 8408184 ),
PARTITION v1 values( 1 ),
PARTITION v2 values( 2 ),
PARTITION vendor_NULL VALUES (NULL),
PARTITION vendor_small VALUES (DEFAULT)
)
as
select a.*
from listing_wide a
where 1=0;
alter table listing_purge modify (
create_date Date default sysdate ,
update_date Date default sysdate
);
Test
----
truncate table listing_wide;
truncate table listing_purge;
insert /*+ append */ into listing_purge
(listing_id, vendor_id, price, qty,hash_lite, hash_code, vendor_listing_id, author, title,subject,isbn_raw, bsa_vend
,match_col)
select
rownum listing_id,
mod(rownum,2) + 1 vendor_id,
1.11 price,
10 qty,
rpad('x',31,'x') hash_lite,
rpad('x',31,'x') hash_code,
rownum vendor_listing_id,
rpad(rownum,400,'x') author,
rpad('x',400,'x') title,
rpad('x',300,'x') subject,
rownum isbn_raw,
mod(rownum,100)||','||mod(Round(rownum/10),100) bsa_vend
,'x' match_col
from dual
connect by level <= 80100;
commit;
set serveroutput on
set linesize 200
begin
tmp_benchmark;
end;
/
select vendor_id, count(*) from listing_wide
group by vendor_id;
select vendor_id, count(*) from listing_purge
group by vendor_id;
Code
----
CREATE OR REPLACE procedure tmp_benchmark
as
l_cnt number := 0; l_upd_rows number := 0;
l_fetch_limit PLS_INTEGER := 1000;
cursor c2 is
select listing_id, vendor_id, price, qty,hash_lite, hash_code, vendor_listing_id, author, title,subject,isbn_raw, bsa_vend, match_col
from listing_purge partition (v2);
TYPE listing_purge_tab is table of c2%rowtype;
lt_book listing_purge_tab;
BEGIN
runStats_pkg.rs_start;
INSERT INTO listing_wide
(listing_id, vendor_id, price, qty,hash_lite, hash_code, vendor_listing_id, author, title,subject,isbn_raw, bsa_vend, match_col)
select listing_id, vendor_id, price, qty,hash_lite, hash_code, vendor_listing_id, author, title,subject,isbn_raw, bsa_vend, match_col
from listing_purge partition (v1);
commit;
runStats_pkg.rs_middle;
-- Open cursor
OPEN c2;
LOOP
fetch c2 BULK COLLECT INTO lt_book LIMIT l_fetch_limit;
FORALL j IN lt_book.FIRST..lt_book.LAST
INSERT into listing_wide
(listing_id, vendor_id, price, qty,hash_lite, hash_code, vendor_listing_id, author, title,subject,isbn_raw, bsa_vend, match_col)
values (lt_book(j).listing_id, lt_book(j).vendor_id, lt_book(j).price, lt_book(j).qty, lt_book(j).hash_lite, lt_book(j).hash_code,
lt_book(j).vendor_listing_id, lt_book(j).author, lt_book(j).title, lt_book(j).subject, lt_book(j).isbn_raw, lt_book(j).bsa_vend
, lt_book(j).match_col)
;
l_upd_rows := l_upd_rows + SQL%RowCount;
exit when lt_book.COUNT < l_fetch_limit;
--exit when c2%notfound;
END LOOP;
close c2;
commit;
runStats_pkg.rs_stop;
EXCEPTION
WHEN others THEN
--u$err.err;
raise;
END;
/
RunStats
--------
Run1 ran in 669 hsecs
Run2 ran in 914 hsecs
run 1 ran in 73.19% of the time
Name Run1 Run2 Diff
STAT...parse count (hard) 2 3 1
LATCH.DML lock allocation 328 327 -1
STAT...sorts (memory) 1 0 -1
STAT...parse time cpu 2 0 -2
LATCH.internal temp table obje 4 2 -2
LATCH.shared pool simulator 6 8 2
LATCH.ASM db client latch 4 6 2
STAT...enqueue releases 1,222 1,224 2
LATCH.space background task la 7 5 -2
STAT...redo log space requests 0 2 2
STAT...Heap Segment Array Upda 146 148 2
STAT...Heap Segment Array Inse 12 14 2
STAT...HSC Heap Segment Block 50,222 50,224 2
STAT...index scans kdiixs1 98 96 -2
STAT...enqueue requests 1,220 1,224 4
STAT...application wait time 0 4 4
STAT...physical read total mul 153 148 -5
STAT...redo ordering marks 1,121 1,126 5
STAT...messages sent 97 105 8
STAT...session logical reads 147,017 147,029 12
STAT...commit cleanouts 3,364 3,376 12
STAT...consistent changes 24 38 14
STAT...db block changes 154,297 154,313 16
LATCH.Consistent RBA 96 80 -16
STAT...parse time elapsed 67 51 -16
LATCH.lgwr LWN SCN 97 78 -19
STAT...redo entries 102,505 102,524 19
STAT...redo buffer allocation 0 22 22
LATCH.post/wait queue 1 31 30
STAT...recursive cpu usage 91 121 30
STAT...redo log space wait tim 0 30 30
STAT...CPU used by this sessio 91 125 34
LATCH.session allocation 7,536 7,574 38
LATCH.In memory undo latch 9 48 39
STAT...physical read total IO 275 235 -40
LATCH.row cache objects 2,688 2,642 -46
STAT...execute count 261 310 49
STAT...consistent gets 19,683 19,632 -51
LATCH.shared pool 138 196 58
STAT...db block gets 127,334 127,397 63
LATCH.undo global data 8,608 8,537 -71
STAT...active txn count during 8,180 8,107 -73
STAT...recursive calls 3,858 3,941 83
LATCH.enqueues 2,034 2,132 98
STAT...user I/O wait time 564 670 106
LATCH.enqueue hash chains 2,525 2,634 109
STAT...table scan rows gotten 50,445 50,315 -130
LATCH.MinActiveScn Latch 16,931 16,796 -135
LATCH.redo allocation 295 467 172
STAT...physical reads 10,010 10,194 184
STAT...Number of read IOs issu 0 184 184
LATCH.cache table scan latch 231 19 -212
STAT...Elapsed Time 669 920 251
STAT...undo change vector size 3,185,292 3,185,592 300
LATCH.redo writing 295 709 414
LATCH.multiblock read objects 464 38 -426
LATCH.object queue header heap 3 451 448
LATCH.active checkpoint queue 2 457 455
LATCH.messages 368 1,250 882
LATCH.cache buffer handles 15,010 13,714 -1,296
STAT...free buffer inspected 10,054 11,522 1,468
LATCH.cache buffers lru chain 20,881 22,578 1,697
LATCH.simulator hash latch 13,132 10,817 -2,315
STAT...hot buffers moved to he 560 3,105 2,545
STAT...IMU Redo allocation siz 8,236 11,044 2,808
STAT...dirty buffers inspected 1 4,860 4,859
LATCH.checkpoint queue latch 201 7,974 7,773
LATCH.cache buffers chains 545,026 536,670 -8,356
LATCH.object queue header oper 34,953 44,292 9,339
STAT...physical reads cache pr 9,735 133 -9,602
STAT...free buffer requested 20,720 10,987 -9,733
STAT...consistent gets from ca 10,729 959 -9,770
STAT...physical reads cache 10,010 184 -9,826
STAT...physical reads direct 0 10,010 10,010
STAT...consistent gets direct 0 10,010 10,010
STAT...consistent gets from ca 19,683 9,622 -10,061
STAT...redo size 78,097,020 78,123,668 26,648
STAT...IMU undo allocation siz 52,228 908 -51,320
STAT...physical read total byt 82,001,920 83,509,248 1,507,328
STAT...physical IO disk bytes 82,001,920 83,509,248 1,507,328
STAT...cell physical IO interc 82,001,920 83,509,248 1,507,328
STAT...physical read bytes 82,001,920 83,509,248 1,507,328
STAT...session pga memory 0 2,162,688 2,162,688
STAT...Effective IO time 0 6,195,022 6,195,022
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
672,749 681,506 8,757 98.72%
PL/SQL procedure successfully completed.
