Oracle Tablespace Design
Oracle Tips by Burleson
Artomatic segment space management (bitmap freelists)
It is a good method for handling objects with
varying row sizes.
It provides better run-time adjustment for
variations in concurrent access and avoids tedious tuning methods.
It provides better multi-instance behavior in
terms of performance/space utilization.
However, note that this automatic feature of
segment space management is available only with locally managed
tablespaces and their objects. A new column called
SEGMENT_SPACE_MANAGEMENT has been added to the dba_tablespaces view
to indicate the segment space management mode used by a tablespace.
Use the Oracle procedure dbms_space.space_usage
to provide the space usage ratio within each block in the Bitmap
Managed Block (BMB) segments. It provides information regarding the
number of blocks in a segment with the following range of free
0-25% free space within a block
25-50% free space within a block
50-75% free space within a block
75-100% free space within a block
One huge benefit of Automatic Segment
Management is the bitmap FREELISTS that are guaranteed to reduce
buffer busy waits. Let's take a close look at this feature.
As a review, a buffer busy wait occurs when a data
block is inside the data buffer cache, but it is unavailable because
it is locked by another DML transaction. A block was unavailable
because another SQL insert statement needed to get a block on which
to place its row.
Without multiple bitmap FREELISTS, every Oracle table and
index had a single data block at the head of the table to manage the
free block for the object. Whenever any SQL insert ran, it had to go
to this block and get a data block on which to place its row.
Obviously, single FREELISTS cause a backup.
When multiple tasks wanted to insert into the same table, they were
forced to wait while Oracle assigned free blocks, one at a time.
Oracle's Automatic Segment Space Management
feature claims to improve the performance of concurrent DML
operations significantly since different parts of the bitmap can be
used, simultaneously eliminating serialization for free space
According to Oracle benchmarks, using bitmap
FREELISTS removes all segment header contention and allows for
super-fast concurrent insert operations (Figure 5.1).
Figure 5.1: Oracle Corporation benchmark on SQL
insert speed with bitmap FREELISTS.
Along with the Automatic Segment Management
features, we get some new tools for the DBA. Let's take a look at
how the Oracle9i DBA will use these tools.
Internal Freelist Management with ASSM
With ASSM, Oracle controls the number of bitmap
FREELISTS, up to 23 per segment. Internally within Oracle, a
shortage of FREELISTS is manifested by a buffer busy wait. This is
because the segment header is available in the data cache, but the
block cannot be accessed because another task has locked the block
to INSERT, DELETE, or UPDATE a row.
Oracle may have a mechanism to
allocate a new segment header block (with another bitmap FREELIST)
whenever buffer busy waits are detected for the segment. As we may
know, Oracle introduced dynamic FREELIST addition in Oracle8i.
Freelist Unlinks - While it is
possible for Oracle to detect the average row length for
segments in a bitmap managed tablespace, Oracle has no way of
predicting how much space to reserve of each data block for row
expansion. This is because Oracle9i has no knowledge of VARCHAR
datatypes that may later be expanded with SQL UPDATE statements.
Logic dictates that Oracle must examine the updated row length
for every UPDATE and relocate the row if it would chain onto
another data block if left on its target block. Row relocation
can have a high overhead, especially for batch-oriented SQL
Freelist Re-links - For Oracle to optimize the threshold
for re-linking a data block, it needs a priori knowledge of the
volume of subsequent INSERT statements. If the threshold is set
too high, only a small amount of space is reserved on the
re-linked data block, and only a few rows can be INSERTED before
Oracle is forced to perform an I/O to grab another data block.
Of course, Oracle9i could detect high-volume
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA