Goal
----
There are 3 approaches to fetch data into schema object collection type.
Benchmark to find the best one.
Solution
--------
Setup test case, benchmark with RunStats and SQL trace.
Result
------
PL/SQL native collection(Associative Array and Nested Table) is the fastest way to get data from SQL.
But some time you need to query against a Collection/Array, you have to declare it as an schema object type.
To fetch data into schema object type collection, here list 3 approaches Pros and Cons:
1) Convert multi columns to object type, bulk collect into collection
Pros: code is simple.
Cons: 50% more CPU time compare to approach 2; about same amount of latches.
2) Use multiset, constructs the required collection of ADTs directly in SQL as a select list element in a single row
Pros: Faster, 50% better than approach 1 in my test case.
Cons: code is a little complex, not support SQL Returning clause in 10.1.
3) bulk collect into PL/SQL native collection, copy data into object type collection
Pros: Fastest, 67% better than approach 1 in my test case, 50% less latches.
Cons: use 100% more PGA memory, code is a little complex and not easy to maintain.
Note: ADT: Abstract Data Type, also calls User Define Datatype
Setup
-----
drop TYPE abelisting.book_chg_tab;
drop TYPE abelisting.book_chg_rec;
create or replace TYPE abelisting.book_chg_rec AS OBJECT
(
listingsid number(20),
qty number(5),
delete_flag number(1),
update_date date
);
/
create or replace TYPE abelisting.book_chg_tab IS TABLE OF abelisting.book_chg_rec;
/
DROP TABLE abelisting.book_delete_stage_part;
CREATE TABLE abelisting.book_delete_stage_part (
listingsid number(20),
qty number(5),
delete_flag number(1),
update_date timestamp(3) default SysTimestamp,
part_mi number(2) default mod(to_number(to_char(sysdate,'MI')),10)
)
PARTITION BY LIST (part_mi) (
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 data_part_6 VALUES (6),
PARTITION data_part_7 VALUES (7),
PARTITION data_part_8 VALUES (8),
PARTITION data_part_9 VALUES (9),
PARTITION dummy_null VALUES (NULL),
PARTITION dummy_other VALUES (DEFAULT)
)
tablespace data_auto
nologging;
insert into /*+ append */ abelisting.book_delete_stage_part(listingsid,delete_flag,update_date, part_mi)
select trunc(dbms_random.value(1,20000)), mod(level,2), trunc(sysdate,'DD') + mod(level,60)/1440,
mod(level,10)
from dual
connect by level <= 30000;
commit;
exec dbms_stats.gather_table_stats(user,'BOOK_DELETE_STAGE_PART');
Code
----
*) RunStats
# multiset vs. bulk collect into object_array
declare
lt_chg abelisting.book_chg_tab;
-- type my_book_chg_tab is table of abelisting.book_delete_stage%ROWTYPE;
-- lt_chg my_book_chg_tab;
type tab_chg_array is table of abelisting.book_delete_stage_part%rowtype;
lt_chg_array tab_chg_array;
begin
runStats_pkg.rs_start;
select cast(multiset(
select book_chg_rec(LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE) from abelisting.book_delete_stage_part
) as book_chg_tab)
into lt_chg
from dual;
end;
/
declare
lt_chg abelisting.book_chg_tab;
type item_chg_array is table of abelisting.book_delete_stage_part%rowtype;
lt_chg_array item_chg_array;
begin
runStats_pkg.rs_middle;
select book_chg_rec(LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE)
bulk collect into lt_chg
from abelisting.book_delete_stage_part
;
runStats_pkg.rs_stop(5);
end;
/
Run1 ran in 24 hsecs
Run2 ran in 32 hsecs
run 1 ran in 75% of the time
Name Run1 Run2 Diff
STAT...recursive calls 11 4 -7
LATCH.active service list 0 7 7
LATCH.cache buffers chains 909 902 -7
LATCH.session idle bit 8 0 -8
STAT...Elapsed Time 26 35 9
STAT...recursive cpu usage 23 34 11
STAT...db block gets 63 51 -12
STAT...db block gets from cach 63 51 -12
STAT...session logical reads 390 377 -13
STAT...workarea memory allocat -34 -9 25
STAT...CPU used when call star 28 0 -28
STAT...CPU used by this sessio 28 0 -28
STAT...DB time 28 0 -28
LATCH.library cache lock 41 12 -29
LATCH.JS queue state obj latch 0 30 30
LATCH.library cache pin 64 24 -40
LATCH.row cache objects 69 27 -42
LATCH.shared pool 58 15 -43
LATCH.enqueues 8 68 60
LATCH.enqueue hash chains 6 69 63
LATCH.library cache 130 36 -94
STAT...undo change vector size 2,808 2,908 100
STAT...redo size 3,956 4,064 108
LATCH.simulator hash latch 2 131 129
STAT...bytes sent via SQL*Net 218 0 -218
STAT...bytes received via SQL* 760 0 -760
STAT...buffer is pinned count 29,796 0 -29,796
STAT...session uga memory max 196,500 0 -196,500
STAT...session pga memory max 6,815,744 393,216 -6,422,528
STAT...session pga memory 0 7,208,960 7,208,960
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,337 1,356 19 98.60%
# multiset vs. plsql_collection copy to object array
declare
lt_chg abelisting.book_chg_tab;
type tab_chg_array is table of abelisting.book_delete_stage_part%rowtype;
lt_chg_array tab_chg_array;
begin
runStats_pkg.rs_start;
select cast(multiset(
select book_chg_rec(LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE) from abelisting.book_delete_stage_part
) as book_chg_tab)
into lt_chg
from dual;
end;
/
declare
lt_chg abelisting.book_chg_tab;
type item_chg_array is table of abelisting.book_delete_stage_part%rowtype;
lt_chg_array item_chg_array;
begin
runStats_pkg.rs_middle;
select LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE, Null
bulk collect into lt_chg_array
from abelisting.book_delete_stage_part;
lt_chg := abelisting.book_chg_tab();
lt_chg.extend(lt_chg_array.count());
For i in lt_chg_array.First() .. lt_chg_array.Last()
Loop
lt_chg(i) := book_chg_rec(lt_chg_array(i).LISTINGSID,
lt_chg_array(i).QTY ,
lt_chg_array(i).DELETE_FLAG,
lt_chg_array(i).UPDATE_DATE
);
End Loop;
runStats_pkg.rs_stop(5);
end;
/
Run1 ran in 27 hsecs
Run2 ran in 17 hsecs
run 1 ran in 158.82% of the time
Name Run1 Run2 Diff
STAT...opened cursors cumulati 8 2 -6
LATCH.active service list 0 7 7
LATCH.child cursor hash table 7 0 -7
STAT...recursive calls 12 4 -8
LATCH.session idle bit 8 0 -8
STAT...Elapsed Time 29 20 -9
STAT...db block gets 63 53 -10
STAT...db block gets from cach 63 53 -10
STAT...recursive cpu usage 23 12 -11
STAT...workarea memory allocat 12 -7 -19
STAT...calls to get snapshot s 62 38 -24
STAT...table fetch by rowid 24 0 -24
STAT...index fetch by key 24 0 -24
LATCH.checkpoint queue latch 8 32 24
STAT...rows fetched via callba 24 0 -24
STAT...CPU used when call star 28 0 -28
STAT...DB time 28 0 -28
STAT...CPU used by this sessio 28 0 -28
LATCH.JS queue state obj latch 0 30 30
STAT...free buffer inspected 0 38 38
STAT...hot buffers moved to he 0 48 48
STAT...buffer is not pinned co 48 0 -48
LATCH.enqueues 10 70 60
LATCH.enqueue hash chains 8 71 63
LATCH.SQL memory manager worka 4 71 67
STAT...consistent gets 397 327 -70
LATCH.cache buffers chains 973 903 -70
STAT...consistent gets from ca 397 327 -70
STAT...consistent gets - exami 72 1 -71
STAT...session logical reads 460 380 -80
STAT...undo change vector size 2,804 2,904 100
LATCH.simulator hash latch 130 3 -127
STAT...redo size 3,952 4,120 168
LATCH.row cache objects 193 18 -175
STAT...bytes sent via SQL*Net 218 0 -218
LATCH.library cache lock 234 12 -222
LATCH.library cache pin 256 25 -231
LATCH.shared pool 258 13 -245
LATCH.library cache 559 39 -520
STAT...bytes received via SQL* 1,067 0 -1,067
STAT...buffer is pinned count 29,796 0 -29,796
STAT...session uga memory 65,464 0 -65,464
STAT...session uga memory max 193,032 68,932 -124,100
STAT...session pga memory max 6,815,744 9,895,936 3,080,192
STAT...session pga memory 65,536 16,646,144 16,580,608
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
2,691 1,330 -1,361 202.33%
*) SQL Trace
ALTER SESSION SET TRACEFILE_IDENTIFIER="yi";
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(waits => TRUE, binds => True);
declare
lt_chg abelisting.book_chg_tab;
begin
select cast(multiset(
select book_chg_rec(LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE) from abelisting.book_delete_stage_part
) as book_chg_tab)
into lt_chg
from dual;
end;
/
declare
lt_chg abelisting.book_chg_tab;
begin
select book_chg_rec(LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE)
bulk collect into lt_chg
from abelisting.book_delete_stage_part
;
end;
/
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE();
tkprof report
-------------
SELECT book_chg_rec(LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE)
FROM
ABELISTING.BOOK_DELETE_STAGE_PART
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.34 0.33 0 316 0 30000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.34 0.33 0 316 0 30000
SELECT CAST(MULTISET( SELECT book_chg_rec(LISTINGSID, QTY, DELETE_FLAG,
UPDATE_DATE)
FROM
ABELISTING.BOOK_DELETE_STAGE_PART ) AS BOOK_CHG_TAB) FROM DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.21 0.22 0 316 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.21 0.22 0 316 0 1
Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 35 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 FAST DUAL (cr=0 pr=0 pw=0 time=9 us)
SELECT LISTINGSID, QTY, DELETE_FLAG, UPDATE_DATE, NULL
FROM
ABELISTING.BOOK_DELETE_STAGE_PART
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.11 0.10 0 316 0 30000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.11 0.10 0 316 0 30000
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 35 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
30000 PARTITION LIST ALL PARTITION: 1 12 (cr=316 pr=0 pw=0 time=90091 us)
30000 TABLE ACCESS FULL BOOK_DELETE_STAGE_PART PARTITION: 1 12 (cr=316 pr=0 pw=0 time=30379 us)
.
