So, here I ran similar SQLs as in my first set of tests :
MSSM :
drop tablespace mssm_tbs including contents and datafiles;
create tablespace mssm_tbs
datafile '/oracle_fs/Databases/ORT24FS/mssm01.dbf' size 1000M
extent management local autoallocate segment space management manual;
undefine sid
col mysid new_value sid
select distinct sid mysid from V$mystat ;
alter session set tracefile_identifier='run_delete_insert_tests_mssm';
alter session set events '10046 trace name context forever, level 8';
alter table source_table cache;
select /*+ FULL (s) */ count(*) from source_table s;
create table target_table_mssm tablespace mssm_tbs as select * from source_table where 1=2;
insert /* first_mssm_insert */ into target_table_mssm select * from source_table;
commit;
delete /* first_mssm_delete */ target_table_mssm;
insert /* reinsert_mssm_insert */ into target_table_mssm select * from source_table;
commit;
create index target_table_mssm_ndx on target_table_mssm(object_id) tablespace mssm_tbs;
delete /* indexed_mssm_delete */ target_table_mssm;
insert /* indexed_mssm_insert */ into target_table_mssm select * from source_table;
commit;
ASSM :
drop tablespace assm_tbs including contents and datafiles;
create tablespace assm_tbs
datafile '/oracle_fs/Databases/ORT24FS/assm01.dbf' size 1000M
extent management local autoallocate segment space management auto;
undefine sid
col mysid new_value sid
select distinct sid mysid from V$mystat ;
alter session set tracefile_identifier='run_delete_insert_tests_assm';
alter session set events '10046 trace name context forever, level 8';
alter table source_table cache;
select /*+ FULL (s) */ count(*) from source_table s;
create table target_table_assm tablespace assm_tbs as select * from source_table where 1=2;
insert /* first_assm_insert */ into target_table_assm select * from source_table;
commit;
delete /* first_assm_delete */ target_table_assm;
insert /* reinsert_assm_insert */ into target_table_assm select * from source_table;
commit;
create index target_table_assm_ndx on target_table_assm(object_id) tablespace assm_tbs;
delete /* indexed_assm_delete */ target_table_assm;
insert /* indexed_assm_insert */ into target_table_assm select * from source_table;
commit;
Rather than repeating the normal INSERT statements (which you can also see in my first set of tests), I will emphasise the INSERTs that followed DELETEs (without intervening COMMITs) :
MSSM :
delete /* first_mssm_delete */ target_table_mssm
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 6.27 12.47 0 5206 326203 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 6.27 12.48 0 5207 326203 303666
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE TARGET_TABLE_MSSM (cr=5206 pr=0 pw=0 time=12478853 us)
303666 TABLE ACCESS FULL TARGET_TABLE_MSSM (cr=4857 pr=0 pw=0 time=911106 us)
insert /* reinsert_mssm_insert */ into target_table_mssm select * from
source_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.00 1.65 0 12367 17122 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.01 1.65 0 12367 17122 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=607452 us)
delete /* indexed_mssm_delete */ target_table_mssm
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 2 0 0
Execute 1 11.01 16.14 671 4325 1243651 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 11.02 16.16 671 4327 1243651 303666
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE TARGET_TABLE_MSSM (cr=4325 pr=671 pw=0 time=16140802 us)
303666 TABLE ACCESS FULL TARGET_TABLE_MSSM (cr=4325 pr=0 pw=0 time=1518587 us)
insert /* indexed_mssm_insert */ into target_table_mssm select * from
source_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 3.09 7.54 63 16018 78398 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.09 7.54 63 16018 78398 303666
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=911112 us)
ASSM :
delete /* first_assm_delete */ target_table_assm
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 7.04 14.30 0 4880 337910 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 7.04 14.30 0 4881 337910 303666
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE TARGET_TABLE_ASSM (cr=4880 pr=0 pw=0 time=14305853 us)
303666 TABLE ACCESS FULL TARGET_TABLE_ASSM (cr=4880 pr=0 pw=0 time=934334 us)
insert /* reinsert_assm_insert */ into target_table_assm select * from
source_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.50 2.02 0 21396 52236 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.50 2.02 0 21396 52236 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=911120 us)
delete /* indexed_assm_delete */ target_table_assm
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 2 0 0
Execute 1 14.84 22.03 672 4502 1258653 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 14.84 22.06 672 4504 1258653 303666
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE TARGET_TABLE_ASSM (cr=4988 pr=673 pw=0 time=22090153 us)
303666 TABLE ACCESS FULL TARGET_TABLE_ASSM (cr=4364 pr=0 pw=0 time=1214785 us)
insert /* indexed_assm_insert */ into target_table_assm select * from
source_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 2.92 5.45 1 26743 117158 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.92 5.45 1 26743 117158 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=911153 us)
The INSERT operations in the ASSM tablespace are more expensive.
MSSM ASSM
Delete
Consistent Gets 5,206 4,880
Current Gets 326,203 337,910
CPU Used (seconds) 6.27 7.04
Insert after Delete
Consistent Gets 12,367 21,396
Current Gets 17,122 52,236
CPU Used (seconds) 1.00 1.50
Delete with Index present
Consistent Gets 4,325 4,502
Current Gets 1,243,651 1,258,653
CPU Used (seconds) 11.01 14.84
Insert after this Delete
Consistent Gets 16,018 26,743
Current Gets 78,398 117,158
CPU Used (seconds) 3.09 2.92
Think carefully before using ASSM for segments that
a) Undergo large Inserts OR Deletes and Inserts
b) Are executed by batch jobs, not multiple,concurrent, online users.