Re: L1 BMB - incorrect freeness status - are they relevant for tables alone?

Hi,

Background:
L1BMB is used to track the freeness of a data block in the extent that this block manages and indicates whether the block is full or how much space usage there is, this is accurate when it comes to table but for indexes, it doesn't actually say whether the space is full or not.

create table temp(roll number, name varchar2(20), mark1 number); create index idx on temp(mark1);
insert into temp select rownum, dbms_random.string(0,20), round(dbms_random.value(0,100)) from dual connect by level < 1000000; commit;
exec dbms_stats.gather_table_stats('VISHNU','TEMP',CASCADE=>TRUE); alter system flush buffer_cache;

During the entire load I checked how many block splits were happening and what kind of block splits were occurring: NAME VALUE

---------------------------------------------------------------- ----------
leaf node splits       3373

most are basically 50-50 block splits. and the total number of blocks of that index is SQL> select sum(blocks) from dba_extents where segment_name='TEMP_IDX'; SUM(BLOCKS)

       3456

now that I have loaded the data, when i check the space usage of the blocks using the dbms_space.space_usage to see the freeness status it showed the following:

FS1 Bytes (at least 0 to 25% free space) = 0
FS1 Blocks(at least 0 to 25% free space) = 0
FS2 Bytes (at least 25 to 50% free space)= 376832
FS2 Blocks(at least 25 to 50% free space)= 46
FS3 Bytes (at least 50 to 75% free space) = 0
FS3 Blocks(at least 50 to 75% free space) = 0
FS4 Bytes (at least 75 to 100% free space) = 0
FS4 Blocks(at least 75 to 100% free space)= 0

it said only 46 blocks have a free space and about 3222 blocks are full, this cannot be true, since the data is loaded into a mark1 column and there are more block splits that resulted in 50-50 block splits.

the tablespace is auto-allocate so I randomly took a extent which is 1MB of size so that a L1BMB tracks the freeness status of the blocks in that extent. Interestingly the L1BMB freeness status is as follows:

   0:Metadata   1:Metadata   2:FULL   3:FULL
   4:FULL   5:FULL   6:FULL   7:FULL
   8:FULL   9:FULL   10:FULL   11:FULL
   12:FULL   13:FULL   14:FULL   15:FULL
   16:FULL   17:FULL   18:FULL   19:FULL
   20:FULL   21:FULL   22:FULL   23:FULL
   24:FULL   25:FULL   26:FULL   27:FULL
   28:FULL   29:FULL   30:FULL   31:FULL
   32:FULL   33:FULL   34:FULL   35:FULL
   36:FULL   37:FULL   38:FULL   39:FULL
   40:FULL   41:FULL   42:FULL   43:FULL
   44:FULL   45:FULL   46:FULL   47:FULL
   48:FULL   49:FULL   50:FULL   51:FULL
   52:FULL   53:FULL   54:FULL   55:FULL