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
leaf node 90-10 splits 0
branch node splits 7
root node splits 1
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
Full Blocks in segment = 3222
Full Bytes in segment = 26394624
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:
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x01c02980 Length: 64 Offset: 0
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
56:FULL 57:FULL 58:FULL 59:FULL
60:FULL 61:FULL 62:FULL 63:FULL
All the blocks are full? now i checked dumped a index block, from that
extent to see the index leaf block header whether the space usage mentioned
in the L1BMB is actually accurate.
End dump data blocks tsn: 4 file#: 7 minblk 10624 maxblk 10624
[oracle@oracle trace]$ alter system dump datafile ^C
[oracle@oracle trace]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 27 10:41:30 2019
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
SQL> alter system dump datafile 7 block 10639;
the dump says:
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 2
kdxconro 314 ---> number of entries in the leaf block.
kdxcofbo 664=0x298 --> offset for starting free space in the block.
kdxcofeo 4264=0x10a8 --> similarly marker to point the end of free space.
kdxcoavs 3600
kdxlespl 0
kdxlende 0
kdxlenxt 29374428=0x1c037dc
kdxleprv 29370843=0x1c029db
kdxledsz 0
kdxlebksz 8032
row#0[4552] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 49
col 1; len 6; (6): 01 c0 2f 67 00 18
row#1[4564] flag: -------, lock: 0, len=12
Obviously there is free space in the block as indicated in the index leaf
block header dump.
Can someone please tell me whether the L1 BMB blocks are used to track
freeness status for tables alone and not for indexes?
Thanks,
Vishnu
