An Experiment on ASSM vs MSSM

I have done a similar experiment on automatic segment space management (ASSM) and manual segment space management (MSSM) as described in the book 'Effective Oracle by Design' (Tom kytes, pp232-234). The test environment is 10gR2 on Windows.

The steps are as follows:

1. Create the tablespace auto_tbs and manual_tbs:

The only difference in the definition is the following clause:
- segement space managemnt auto
- segement space management manual

2. Create two tables auto_table and manual_tables in these two tablespaces respectively

3. Perform insert/delete to these two tables for about 15 min with 10 simutaneous sessions

4. Before and after insert/delete, taking AWR snapshots

5. Analyze the results:

(a) Summary


- In the case of MSSM, buffer busy wait is more significant than in the case of ASSM, 3,831 vs 1,033.

In the case of heavy insert, these wait are induced by freelist contention. When using ASSM, this contention could be dramatically reduced. For MSSM to achieve same effects, we may need to fine-tuning with freelists and freelist groups.

- ASSM shows larger wait time for read by other session event than MSSM

- In both case, the number of rows inserted is close. With respect to this effect, ASSM and MSSM have not much difference. The freelist contention as seen in the case of MSSM play a little role.

(b) Notes about read by other session
http://www.dba-oracle.com/oracle_news/2006_11_04_read_other_session_waits.htm

"My understanding is that other concurrency related waits such as freelists are still reported as 'buffer busy waits'."

From this experiment, I agree with the above statement by a commentator in the blog (http://blogs.sun.com/glennf/entry/wait_events_renamed_in_10g)


Results
========

(a) manual_table

scott@ORCL> select count(*) from manual_table;

 COUNT(*)
----------
  2412894


Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                  ---------------       ---------------
                  Executes:                366.50                  3.29
              Transactions:                111.35


Top 5 Timed Events                                        Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file sequential read               9,190         965    105   12.7   User I/O
CPU time                                            617           8.1
enq: TX - contention                    344         588   1710    7.8      Other
read by other session                 8,476         553     65    7.3   User I/O
buffer busy waits                     3,831         243     63    3.2 Concurrenc





(b) auto_table

scott@ORCL> select count(*) from auto_table;

 COUNT(*)
----------
  2522176


Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                  ---------------       ---------------
                  Executes:                383.64                  3.38
              Transactions:                113.63



Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file sequential read              10,324       1,417    137   16.7   User I/O
read by other session                10,431         731     70    8.6   User I/O
CPU time                                            677           8.0
enq: HW - contention                  1,008         557    553    6.6 Configurat
log file parallel write               4,104         192     47    2.3 System I/O



                                                                  Avg
                                            %Time  Total Wait    wait     Waits
Event                                 Waits  -outs    Time (s)    (ms)      /txn
---------------------------- -------------- ------ ----------- ------- ---------
db file sequential read              10,324     .0       1,417     137       0.1
...
buffer busy waits                     1,033     .0          48      47       0.0