Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Characteristics of Bitmap Segment
Management


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:

Value

Meaning

0000

Unformatted Block

0001

Block is logically full

0010

<25% free space

0011

>25% but <50% free space

0100

> 50% but <75% free space

0101

>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 lookups.

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 management.

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 concurrency problems.

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:

  1. Extent control header blockcontains four components:
    1. The extent map of the segment
    2. The ďlastĒ block at each level of the B-tree
    3. The low high-water mark
    4. The high high-water mark
  2. First-level bitmap blockThis contains a list of all first-level data block addresses, and the 4-bit free space indicator for each block.
  3. Second-level bitmap blockThis 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 blocks

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 INSERTs.

Next, letís look at some tolls for automated space management in Oracle.

Oracle Tools for Automatic Space Management

Oracle provides several new procedures for automated space management. These include:

  • dbms_space.space_usage

  • dbms_repair.rebuild_freelists

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.

select
   blocks,
   empty_blocks
from
   dba_tables
where
   table_name='CUSTOMER';

Here we see the total number of blocks and the number of empty blocks with the existing segment extents.

      BLOCKS EMPTY_BLOCKS                     
                                 
  ---------- ------------            
                                                                                
          10            3      

You can also call dbms_space.unused_space to gather this information on used data blocks:

set serveroutput on
 
 declare
   TOTAL_BLOCKS          number;
   TOTAL_BYTES           number;
   UNUSED_BLOCKS         number;
   UNUSED_BYTES          number;
   LAST_USED_EXTENT_FILE_ID  number;
   LAST_USED_EXTENT_BLOCK_ID number;
   LAST_USED_BLOCK       number;
 
 begin dbms_space.unused_space('SYSTEM', 'TEST', 'TABLE',
        TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
        LAST_USED_EXTxENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
        LAST_USED_BLOCK);
 
        dbms_output.put_line('OBJECT_NAME = CUSTOMER');
        dbms_output.put_line('----------------------');
        dbms_output.put_line('TOTAL_BLOCKS      = ||TOTAL_BLOCKS);
        dbms_output.put_line('UNUSED_BLOCKS     = '||UNUSED_BLOCKS);
    end;
    /

Here we see the total and unused blocks for the segment.

OBJECT_NAME       = CUUSTOMER                
                              
-----------------------------------                                            
TOTAL_BLOCKS      = 13                                                 
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.

dbms_repair.rebuild_freelists('SCOTT','EMP');

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.

select
   class,
   count,
   time
from
   v$waitstat
where
   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.

CLASS       COUNT    TIME
------------------      ---------- ----------
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.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter