I guess that ASSM makes sense only in a really very high concurrency environment -- where multiple sessions need to manipulate the FreeList for the same segment (table) concurrently. Some new features (eg ALTER TABLE SHRINK) require ASSM. Going forward with ASSM being the "default" default in 10g and 11g, I guess that it is Oracle's direction.
However, for those of us who do need to get performance out of our systems, ASSM can be a drag.
i. It seems to increase current gets when doing INSERTs
ii. Data isn't clustered into contigous blocks as it is inserted. This is important for index range scans on an indexed column that is incremented for each insert (eg a DATE or SEQUENCE). The Clustering Factor goes "bad"
Here are some simple tests that I ran, running the same INSERT (and CREATE INDEX) statements on two copies of a "target" table, one in MSSM and the other in ASSM.
Running this in one session :
set echo on
set timing on
spool run_insert_tests_mssm
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_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;
insert /* second_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;
insert /* indexed_mssm_insert */ into target_table_mssm select * from source_table;
commit;
select segment_name, segment_type, blocks from user_segments where segment_name like '%_MSSM%' order by segment_name ;
and running this in another session :
set echo on
set timing on
spool run_insert_tests_assm
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_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;
insert /* second_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;
insert /* indexed_assm_insert */ into target_table_assm select * from source_table;
commit;
select segment_name, segment_type, blocks from user_segments where segment_name like '%_ASSM%' order by segment_name;
Here's a comparison of the results :
For the first INSERT statement :
insert /* first_mssm_insert */ into target_table_mssm select * from
source_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.03 0 0 0 0
Execute 1 1.32 2.98 0 8771 23618 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.33 3.01 0 8771 23618 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=607550 us)
insert /* first_assm_insert */ into target_table_assm select * from
source_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.04 0 0 0 0
Execute 1 2.39 2.85 0 11917 38462 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.42 2.90 0 11917 38462 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=911236 us)
The number of blocks (and rows) read from the SOURCE_TABLE is exactly the same (4,193 ,with no physical reads as the table had been cached by previous queries).
Yet, the INSERT measured up as :
MSSM ASSM
Consistent Gets 8,771 11,917
Current Gets 23,618 38,462
CPU Used (seconds) 1.32 2.39
That means that the Insert into an ASSM Tablespace causes more Current Gets and CPU usage.
These are the results of the second INSERT statement :
insert /* second_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.58 2.70 0 8758 23393 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.58 2.70 0 8758 23393 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=911108 us)
insert /* second_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.33 3.19 0 12254 37667 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.34 3.19 0 12254 37667 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=607515 us)
Once again, we see a siginficant difference in Current Gets.
MSSM ASSM
Consistent Gets 8,758 12,254
Current Gets 23,393 37,667
CPU Used (seconds) 1.58 1.33
Next is the CREATE INDEX statement :
create index target_table_mssm_ndx on target_table_mssm(object_id) tablespace
mssm_tbs
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.06 0 1 0 0
Execute 1 1.62 2.11 0 9357 1688 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.63 2.18 0 9358 1688 0
create index target_table_assm_ndx on target_table_assm(object_id) tablespace
assm_tbs
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 9 0 0
Execute 1 1.83 2.20 0 9535 2188 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.84 2.21 0 9544 2188 0
Again, the ASSM performed poorer.
The final test is the Insert with the Index present.
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.12 4.26 1342 16682 119228 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.12 4.27 1342 16682 119228 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=607454 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 3.70 5.60 1342 17923 135324 303666
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.70 5.60 1342 17923 135324 303666
Rows Row Source Operation
------- ---------------------------------------------------
303666 TABLE ACCESS FULL SOURCE_TABLE (cr=4193 pr=0 pw=0 time=911107 us)
Again, the ASSM Insert performs poorer.
MSSM ASSM
Consistent Gets 16,682 17,923
Current Gets 119,228 135,324
CPU Used (seconds) 3.12 3.70
These are the final sizes :
SQL> select segment_name, segment_type, blocks from user_segments where segment_name like '%_MSSM%' order by segment_name ;
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------------ ------------------ ----------
TARGET_TABLE_MSSM TABLE 13312
TARGET_TABLE_MSSM_NDX INDEX 2688
SQL> select segment_name, segment_type, blocks from user_segments where segment_name like '%_ASSM%' order by segment_name;
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------------------ ------------------ ----------
TARGET_TABLE_ASSM TABLE 13312
TARGET_TABLE_ASSM_NDX INDEX 2816
2 comments:
What version of Oracle and what OS was this test performed on?
10.2.0.4 on 64bit OEL 5.1
Post a Comment