In this test, our goal is to archive a table called archsim_smt under schema dennis. We delete the rows from this table and insert the rows into another table under dennis_arch schema. The row that is selected to be delete/insert is based on column order_id. Each order_id has 100 rows in the table archsim_smt. Order_id has a FK constraint with reference to the table archsim_ssd. Each master_order_id in the table archsim_ssd has 50 order_id. So when we input a particular master_order_id, we will end up archiving 5000 rows in the table archsim_smt.
The objects used in the test are set up by following script:
----- setup.sql ---------------
-- create two tables in dennis
create table dennis.archsim_ssd
nologging
as
select
rownum order_id,
1+ mod(rownum,20) master_order_id,
rpad(rownum,10) small_vc_ssd,
rpad(rownum,&m_pad) padding_ssd
from all_objects
where rownum <= 1000
;
/* one master_order_id has 50 order_id;
one order_id has 100 id_smt;
*/
create table dennis.archsim_smt
nologging
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 1000
)
select
rownum id_smt,
1+mod(rownum,1000) order_id,
trunc((rownum-1)/5) small_num_smt,
rpad(rownum,10) small_vc_smt,
rpad(rownum,&m_pad) padding_smt
from
generator v1,
generator v2
where rownum <= 100000
;
-- create two empty table in dennis_arch
create table dennis_arch.archsim_ssd
as select * from dennis.archsim_ssd
where 1=0;
create table dennis_arch.archsim_smt
as select * from dennis.archsim_smt
where 1=0;
create unique index dennis.archsim_ssd_pk on dennis.archsim_ssd(order_id);
create unique index dennis.archsim_smt_pk on dennis.archsim_smt(id_smt);
alter table dennis.archsim_ssd add constraint archsim_ssd_pk primary key(order_id);
alter table dennis.archsim_smt add constraint archsim_smt_pk primary key(id_smt);
alter table dennis.archsim_smt add constraint archsim_smt_fk foreign key(order_id) references dennis.archsim_ssd;
begin
dbms_stats.gather_table_stats(
user,
'archsim_ssd',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
begin
dbms_stats.gather_table_stats(
user,
'archsim_smt',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/
This test was performed by executing a script: comp_test.sql, which is shown below.
-------------- Script: comp_test.sql -------------------
rem comp_test.sql
rem
rem Comparing two methods to archive a table archsim_smt in
rem the dennis schema.
rem
rem
rem Two methods are different base on how to select the rows for archiving:
rem
rem Method 1 - single SQL
rem
rem insert into dennis_arch.archsim_smt
rem select * from archsim_smt
rem where order_id in (
rem select order_id
rem from archsim_ssd
rem where master_order_id = 1 );
rem
rem delete from dennis.archsim_smt
rem where order_id in (
rem select order_id
rem from archsim_ssd
rem where master_order_id = 1 );
rem
rem commit;
rem
rem Method 2 - PLSQL LOOP
rem ( similar approach is used in the production database right now)
rem declare
rem inorderid number;
rem cursor allorderids is
rem select distinct order_id
rem from dennis.archsim_ssd
rem where master_order_id = 1;
rem begin
rem open allorderids;
rem
rem FETCH allorderids INTO inorderid;
rem
rem WHILE allorderids % FOUND
rem LOOP
rem INSERT
rem INTO dennis_arch.archsim_smt
rem SELECT *
rem FROM dennis.archsim_smt
rem WHERE order_id = inorderid;
rem
rem delete from dennis.archsim_smt
rem where order_id = inorderid;
rem
rem inorderid := NULL;
rem FETCH allorderids INTO inorderid;
rem END LOOP;
rem
rem commit;
rem close allorderids;
rem end;
rem /
rem
rem A tool called Runstat is used to compare two methods.
rem
rem Runstats is a tool developed by Tom Kyte to compare two
rem different methods of doing the same thing and show which
rem one is superior. (http://asktom.oracle.com/tkyte/runstats.html)
rem
rem Test was done in a 11 g database.
rem
spool comp_test
exec runstats_pkg.rs_start;
-- method 1
@@setup
@@method_1
select count(*) from dennis.archsim_smt;
select count(*) from dennis_arch.archsim_smt;
exec runstats_pkg.rs_middle;
-- method 2
@@setup
@@method_2
select count(*) from dennis.archsim_smt;
select count(*) from dennis_arch.archsim_smt;
exec runstats_pkg.rs_stop;
Results
Run1 ran in 1339 hsecs
Run2 ran in 3333 hsecs
run1 ran in 40.17% of the time
Name Run1 Run2 Diff
STAT...write clones created in 0 1 1
LATCH.PL/SQL warning settings 123 122 -1
LATCH.cp sga latch 1 0 -1
LATCH.ncodef allocation latch 1 0 -1
LATCH.threshold alerts latch 0 1 1
LATCH.begin backup scn array 301 300 -1
LATCH.dml lock allocation 13 12 -1
LATCH.sort extent pool 60 59 -1
LATCH.deferred cleanup latch 1 0 -1
...
STAT...redo size 7,364,552 7,400,712 36,160
LATCH.object queue header oper 33,375 70,180 36,805
LATCH.simulator lru latch 7,650 69,629 61,979
LATCH.simulator hash latch 13,851 125,011 111,160
STAT...consistent gets from ca 55,489 665,732 610,243
STAT...no work - consistent re 53,477 665,735 612,258
STAT...table scan blocks gotte 45,247 657,746 612,499
STAT...consistent gets from ca 60,627 674,250 613,623
STAT...consistent gets 60,627 674,250 613,623
STAT...session logical reads 100,356 714,181 613,825
LATCH.cache buffers chains 197,356 851,159 653,803
STAT...table scan rows gotten 726,518 10,526,397 9,799,879
STAT...physical read total byt 109,912,064 182,550,528 72,638,464
STAT...physical read bytes 109,502,464 182,140,928 72,638,464
Run1 latches total versus Run2 -- different and pct
Run1 Run2 Diff Pct
391,297 1,279,691 888,394 30.58%
It can be seen from the the Runstat tool, the method 1 has more than 10 times less consistent gets than method 2 ( 60,627 vs 674,250); and also method 1 has much less latches than method 2 (391,297 vs 1,279,691 ). So it is clear that Method 1 is superior. As Tom Kyte said, if someting can be done in simple sql statements,it will be better than PL/SQL precedures generally.
