JL Computer Consultancy

Problems with ASSM and bitmap indexes. Version: 9.2.0.2

February 2003


Everyone knows - or should know - that single row DML can have a serious impact on the size and efficiency of bitmap indexes. This is on top of the standard problem of lock collisions that occur when multiple concurrent processes update the indexex columns of apparently unrelated rows. (See article on dbazine for more details)

However, if you do happen to have some code which does this, beware of a bug in the handling of tablespaces that use Automatic Segment Space Management - a feature of Oracle 9i that replaces freelists and freelist groups with multiple bitmap blocks per segment to indicate to processes which data block to use when inserting data.

Under automatic segment space management (a.k.a ASS management), Oracle keeps a map for each extent in a segment showing how much space (to a fairly coarse granularity) is available is in each block in that segment. When a process needs to insert a new row, Oracle uses the process ID to determine which block in a map the process should use. Because the new algorithm allows for multiple insertion blocks (rather than just the one block at the top of each freelist) this introduces an easy way to reduce the contention on highly concurrent inserts. However, there are a few implementation details which may introduce side-effects that should be considered.

One implementation detail is that instead of the old 'raise the high water mark' your process may format a 16-block chunk of the current extent - which need not be the next unused 16 blocks. Oracle is supposed to keep track of the formatted and unformatted space in an extent to ensure that new extents are not allocated unless it is necessary - which should mean that, typically, all such space is formatted before a new extent is allocated.

Unfortunately, there seem to be some holes in the mapping mechanism, or some other part of the allocation algorithm, that allow Oracle to allocate new extents even when there is plenty of free space in existing extents. In 9.2 this shows up (only, I believe) under an extreme abuse of bitmap indexes as shown in the code below.

Back to Main Index of Topics


rem
rem     Script:        bust_bits.sql
rem     Author:        Jonathan Lewis
rem     Dated:         15-Dec-2002
rem     Purpose:       Bitmap updates are even more catastrophic in ASSM tablespaces
rem
rem     Notes:
rem     ------
rem     The user of this script has to be able to create a tablespace
rem     DON'T modify this script to point at an existing tablespace,
rem     especially if it contains files marked as autoextend.
rem
rem     Modify the script to match your system, and run it.
rem     You will find that the ASSM tablespace gets filled, and
rem     but the bitmap index has 'wasted' most of the allocated space
rem
 
create  tablespace assm datafile 'c:\oracle\oradata\d9201\assm.dbf' 
size 21m 
blocksize 8k
extent management local 
uniform size 4m
segment space management auto
;
 
create table t1 (
        id,
        bit_col,
        constraint t1_pk primary key (id)
) as
select  rownum, mod(rownum,2) 
from    all_objects
where   rownum <= 10000
;
 
create bitmap index t1_bi on t1(bit_col) tablespace assm;
analyze index t1_bi compute statistics;
 
validate index t1_bi;
 
 
rem
rem     You will find that this index uses one block, and
rem     about 2,900 bytes from that block
rem
 
select 
        height, del_lf_rows, lf_rows, 
        btree_space, used_space
from 
        index_stats
;
 
 
rem
rem     Now do some highly unsuitable single row updates
rem     Your session will very quickly crash with an error:
rem     ORA-01654: unable to extend index by 512 ...
rem
 
begin
        for i in reverse 1..500 loop
               update t1 set bit_col = 1
               where bit_col = 0
               and id = i;
        end loop;
end;
/
 
validate index t1_bi;
 
select 
        height, del_lf_rows, lf_rows, 
        btree_space, used_space
from 
        index_stats
;
 
select extent_id, blocks , bytes
from user_extents
where segment_name = 'T1_BI'
order by extent_id
;
 
rem
rem     You will find that on one hand, the index claims
rem     about 700K (wow) of space, with 9.5K used.  But the
rem     extent allocation shows 20M allocated
rem
 
 
spool off

Back to Main Index of Topics


14th Feb 2003