A test case - Compare two methods to archive a table

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.