ASSM, freelists and PCTFREE
Oracle Database Tips by Donald Burleson
Oracle introduced Automatic Segment Storage Management (ASSM) as a
replacement for traditional freelists management which used one-way
linked-lists to manage free blocks with tables and indexes. ASSM
is commonly called "bitmap freelists" because that is how Oracle
implement the internal data structures for free block management.
Do not confuse ASSM (bitmap freelists) with Automatic Storage Management (ASM).
As of Oracle 10g, BC does not always recommend implementing bitmap freelists
for high DML tables because there is a tradeoff between reduced buffer busy waits and DML contention
during high update activity.
Note: As of 11g release
2, many of the performance bugs in ASSM have been repaired. However,
it is still important to test ASSM if you use it for tables that experience
high-volume DML updates.
The bitmap freelists of ASSM greatly
reduce segment header contention and improve simultaneous insert concurrency
(up to a point). ASSM also removes the need to specify freelist
groups in RAC.
The new dbms_space procedures allow the DBA to see growth trends within
specific objects, and ASSM provides better multi-instance RAC behavior in
terms of performance/space utilization.
Simpler object management with ASSM
ASSM provides administrative ease of use by
avoiding the specification of storage parameters and ASSM is a very efficient method for handling objects
with varying row sizes. Lastly, ASSM provides better run-time adjustment
for variations in concurrent access and avoids tedious tuning methods.
Why use ASSM?
Automatic segment space management (ASSM)
is a simpler and more efficient way of managing space
within a segment. It completely eliminates any need to
specify and tune the pctused, freelists, and
freelist groups storage parameters for schema
objects created in the tablespace. If any of these
attributes are specified, they are ignored.
ASSM is not for every database, especially those with super-high DML
Pros of ASSM:
ASSM is better than a static pctused. The bitmaps make
ASSM tablespaces better at handling rows with wide variations in row
: ASSM will remove buffer busy
waits better than using multiple freelists. When a table has
multiple freelists, all purges must be parallelized to reload
the freelists evenly, and ASSM has no such limitation.
The bitmap freelists remove the need to
define multiple freelists groups for RAC and provide overall
improved freelist management over traditional freelists.
Cons of ASSM:
Several studies have shown that large-table full-table
scans (FTS) will run longer with ASSM than standard bitmaps. ASSM
FTS tablespaces are consistently slower than freelist FTS
operations. This implies that ASSM may not be appropriate for
decision support systems and warehouse applications unless
partitioning is used with Oracle Parallel Query.
: Numerous experts have
conducted studies that show that tables with high volume bulk loads
perform faster with traditional multiple freelists.
For row ordered tables, ASSM can adversely
for indexes. Bitmap freelists are less likely to place
adjacent tows on physically adjacent data blocks, and this can lower
the cost-based optimizer's propensity to favor an index range scan.
According to Oracle benchmarks, using bitmap FREELISTS removes all segment
header contention and allows for super-fast concurrent insert operations:
For each extent
in the segment, Oracle ASSM keeps an entry pointing to the bitmap for that
ASSM Segment header extent map points to all extent bitmaps in segments.
Oracle ASSM also has pointers to the last bitmap block within each logical
ASSM has 6 different status flags for table blocks:
0 = unformatted
1 = logically full (per pctfree)
2 = 0-25% free
3 = 25-50% free
4 = 50%-75% free
5= 75-100% free
As rows are inserted, ASSM updates his ?level 1 bitmap block? when the new
free space crosses one of these six thresholds. It's also important to note that
blocks are marked as full as soon as the last insert/update crosses the PCTFREE
threshold, the point at which ASSM un-links the data block from the freelist
For example, assume the default PCTFREE=20, which specifies an freelist
unlink when the block is 80% full. Whether an insert takes the ?real? free space
to 81% or 99%, Oracle marks the block as full, without regard to the amount of
Hence you cannot assume that the ?real? free space kept in reserve for row
expansion is 20%; it depends on several factors, most notably the block size and
average row length for the table.
Adjusting the behavior of ASSM (guru's
According to Oracle expert
there are some undocumented hidden parameters in ASSM to control it's
behavior: (Note: Never change hidden parameters without the consent of
Oracle Technical Support):
- _walk_insert_treshold (default 5)
Freelist blocks to scan before turning to higher level list or bump HWM
(if walking on TFL, PFL and MFL are searched next)
- _release_insert_threshold (default 5)
How many unsuitable blocks to unlink from freelist before bump HWM
See my other notes on ASSM here:
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.