Tablespace one way freelist internals
Oracle Tips by Burleson
Oracle Tablespace Design
FREELISTS blocks much like a B-tree index,
multiple transactions can simultaneously access free blocks without
locking or concurrency problems.
As we have noted, the purpose of bitmap blocks
are to track the free blocks in the segment. Since the free blocks
are organized in a B-tree, we see the following nodes inside the
segment control block. There are three data blocks that comprise the
The extent control header block contains the
The extent map of the segment
The "last" block at each level of the
The low high-water mark
The high high-water mark
The high-water mark in the segment header has
also changed in Oracle9i bitmap blocks. Instead of having a single
pointer to the highest free block in an object, the B-tree index
structure allows for a range of high-water mark blocks. Hence, we
see two pointers for the high-water mark.
The low high-water mark (LHWM) — All
blocks below this block have been formatted for the table.
The high high-water mark (HHWM) —
All blocks above this block have not been formatted. Internally,
the HHWM is required to ensure that Oracle direct load
operations can access contiguous unformatted blocks.
Let's look at each block in detail to
understand how space is managed in bitmap segment control. The
Extent Control Header Block contains the high high-water mark, the
low high-water mark, the extent map, and the data block addresses
for each of the three levels of bitmap blocks.
The extent map lists all of the data block
address for each block within each extent within the segment and
shows the four-bit free space of each block within the extent. Since
the extent size is controlled by Oracle9i locally-managed
tablespaces, each extent size within the tablespace is uniform,
regardless of the NEXT extent size for each object in the
Note that the first three blocks of the first
extend list (blocks 0 — 2) are used for metadata and are not
available for segment block addresses. For each extent in the
segment, Oracle9i keeps an entry pointing to the bitmap for that
segment (Figure 5.3).
Figure 5.3: Segment header extent map points to
all extent bitmaps in segments.
Oracle9i also has pointers to the last bitmap
block within each logical bitmap level (Figure 5.4).
Figure 5.4: Pointers to last bitmap block on each bitmap level.
This new pointer structure allows Oracle9i to
quickly access multiple bitmaps to improve concurrency of
Potential Performance Issues with ASSM
The Oracle9i community has mixed feelings about
using ASSM tablespaces. Among the top points about ASSM, we find
both Pros and Cons:
Pros of ASSM:
Varying row sizes — ASSM is better
than a static PCTUSED. The bitmaps make ASSM tablespaces better
at handling rows with wide variations in row length.
Reducing buffer busy waits — ASSM
will remove buffer busy waits better than using multiple
FREELISTS. As we may know, when a table has multiple FREELISTS,
all purges must be parallelized to reload the FREELISTS evenly,
and ASSM has no such limitation.
Great for RAC — The bitmap FREELISTS
remove the need to define multiple FREELISTS groups for RAC, and
provide overall improved FREELIST management over traditional
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA