Question: I have buffer busy waits that are from
data block contention. Should I use ASSM and use bitmap freelists? If not,
should I add freelists or freelist groups to the table? How many multiple
freelists should I define?
Answer: With ASSM (Automatic Segment Storage
Management) a.k.a. bitmap freelists, you don't have as many buffer waits, but
you may experience
issues with ASSM DML throughput in super-high volume update tables.
You should define just enough extra freelists to stop the
buffer busy waits, using the high-water mark of concurrent updates against the
table as a guideline. Here are my notes on
Oracle freelist management.
But what if you over allocate freelists? You add additional
segment header blocks to the table, and there may be a tiny increase in DML
overhead to manage the freelists. Also,
Rich Niemiec has suggested that adding freelist groups instead of freelists
may help.
I might suggest that you adopt an iterative process to
determine the optimum number of freelists:
-
Add freelists one at a time, during low usage times:
alter
table mytable storage (freelists 2);
-
Measure the buffer wait changes during peak processing
(statspack)
-
Repeat until buffer waits disappear
Multiple freelist references
According to MOSC Note: 1060377.6 we see the basic
principle behind multiple freelists:
It is a good idea to always use the freelists option when creating a table that
will have high inserts or updates requiring new space contention because this
creates multiple process (segment) free lists instead of the default ONE
master free list on a segment header.
From MOSC note: 1029850.6 we see that excessive
freelists can cause sub-optimal storage utilization:
As can be seen from the algorithms above, using multiple free lists may cause
some empty blocks to go unused, causing the segment to extend. If performance is
critical, multiple free lists can be used to improve concurrent access, possibly
at the expense of additional space used.
My multiple freelist notes:
Here are my related notes on adding multiple freelists: