Characteristics of Bitmap
Oracle Tips by Burleson
Bitmap space management uses four bits inside
each data block header to indicate the amount of available space in
the data block. Unlike traditional space management with a fixed
relink and unlink threshold, bitmap space managements allow Oracle to
compare the actual row space for an INSERT with the actual available
space on the data block. This enables better reuse of the available
free space especially for objects with rows of highly varying size.
Here are the values inside the 4 bit space:
Block is logically full
<25% free space
>25% but <50% free space
> 50% but <75% free space
>75% free space
Table 10-6: Bitmap value meanings
The value of this bitmap indicates how much
free space exists in a given data block. In traditional space
management, each data block must be read from the freelist to see if
it has enough space to accept a new row. In Oracle, the bitmap is
constantly kept up-to-date with changes to the block, and reducing
wasted space because blocks can be kept fuller since the overhead of
freelist processing has been reduced.
Another enhancement of Oracle space
management is that concurrent DML operations improve significantly.
This is because different parts of the bitmap can be used
simultaneously, thereby eliminating the need to serialize free space
Please note that Oracle segment control
structures are much larger than traditional freelist management.
Because each data block entry contains the 4-byte data block address
and the 4-bit free space indicator, each data block entry in the space
management bitmap will consume approximately 6 bytes of storage.
It is also
important to note that space management blocks are not required to be
the first blocks in the segment. In Oracle8, the segment headers were
required to be the first blocks in the segment. In Oracle8i this
restriction was lifted and the DBA could allocate additional freelists
with the alter table command. In Oracle, Oracle automatically
allocates new space management blocks when a new extent is created and
maintains internal pointers to the bitmap blocks (Figure 10-1).
Figure 10-79: Non-contiguous bitmap blocks
within a segment
Oracle Freelist Internals
Just like traditional freelists, the bitmap
block (BMB) is stored in a separate data block within the table or
index. Because Oracle does not publish the internals of space
management, we must infer the structure from block dumps. Hence, this
information may not be completely accurate, but it will give us a
general idea about the internal mechanisms of Oracle automatic space
Unlike a linear-linked list in traditional
freelists, bitmap blocks are stored in a B-tree structure, much like a
B-tree index structure. This new structure has important ramifications
for concurrent DML. In traditional freelists, free blocks must be
accessed one-at-a-time, and this causes segment header contention in
applications with high-volume INSERT operations. Because Oracle can
use the freelists blocks much like a B-tree index, multiple
transactions can simultaneously access free blocks without locking or
Now letís look inside the segment header and
take a closer look at the bitmap space management techniques.
Segment Extent Control Header
As we have noted, the purpose of the 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 segment control:
- Extent control header block contains
- The extent map of the segment
- The ďlastĒ block at each level of the B-tree
- The low high-water mark
- The high high-water mark
- First-level bitmap block This
contains a list of all first-level data block addresses, and the
4-bit free space indicator for each block.
- Second-level bitmap block This
contains a list of all second-level data block addresses, and the
4-bit free space indicator for each block.
New High-Water Mark Pointers
The high-water mark in the segment header has
also changed in Oracle 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 requires to ensure that Oracle direct load operations can
access contiguous unformatted blocks.
As we see in
Figure 10-2, Oracle maintains several sets of pointers to bitmap
blocks in the segment header.
Figure 10-80: Segment header pointers to bitmap
Letís look at each block in detail to
understand how space is managed in bitmap segment control.
Extent Control Header Block
This 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 4-bit free-space of each block within the extent. Since the extent
size is controlled by Oracle locally-managed tablespaces, each
extent size within the tablespace is uniform, regardless of the NEXT
extent size for each object in the tablespace.
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, Oracle keeps an entry pointing to the bitmap for
that segment (Figure 10-3).
Figure 10-81: Segment header extent map points
to all extent bitmaps in segments
Oracle also has pointers to the last bitmap block within each
logical bitmap level (Figure 10-4).
Figure 10-82: Pointers to last bitmap block on
each bitmap level
This new pointer structure allows Oracle to
quickly access multiple bitmaps to improve concurrency of high-volume
Next, letís look at some tolls for automated
space management in Oracle.
Oracle Tools for Automatic
Oracle provides several new procedures for
automated space management. These include:
The most important procedure in the dbms_space
package is the space_usage procedure. As we may recall, we can get
information directly from DBA_TABLES on block usage.
Here we see the total number of blocks and the number of
empty blocks with the existing segment extents.
You can also call dbms_space.unused_space to gather this
information on used data blocks:
begin dbms_space.unused_space('SYSTEM', 'TEST', 'TABLE',
TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
dbms_output.put_line('OBJECT_NAME = CUSTOMER');
dbms_output.put_line('TOTAL_BLOCKS = ||TOTAL_BLOCKS);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
Here we see the total and unused blocks for the segment.
UNUSED_BLOCKS = 0
Oracle also provide a
dbms_repair.rebuild_freelists procedure. The purpose of the
rebuild_freelists procedure is to coalesce bitmap freelist blocks onto
the master freelist and zero-out all other freelists for the segment.
For segments accesses by Real Application Clusters (e.g. multiple
freelist groups), Oracle will evenly distribute all free blocks
among the existing freelist groups.
Here is an example of this procedure being used
to rebuild the freelists for the EMP table.
Next, letís look at Oracle views that relate
to bitmap space management.
Oracle Views for Bitmap Freelists
Oracle also has several v$ and x$ views that
display the status of freelists. The transaction freelist is stored
inside the X$KVII.KTSMTF view, and the v$waitstat view contains
information on bitmap freelists.
class like 'bitmap%';
Here we see all system-wide waits associated with
bitmap blocks or bitmap index blocks. With the multiple bitmap feature
we should seldom see any waits because multiple bitmap freelists are
available for concurrent DML.
------------------ ---------- ----------
bitmap block 0 0
bitmap index block 0 0
As we can see, Oracle offers some great
automated storage enhancements, but we must remember that this is an
optional feature. However, the vast majority of legacy systems on
Oracle continue to utilize traditional space management. This next
section will explore the features of traditional space management.
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts.