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

Thanks Jonathan,

I was wondering whether we can use this dbms_space as an alternative which is very less resource intensive than running a segment advisor to track the space saving of an index following an index rebuild or shrink space or coalesce as in few environments they disable the automatic advisor tasks and there are few other challenges inherent to the way the data is stored in index leaf blocks.

The reason for this thought is because of this: Usually in an OLTP environment, we add data files when we run out of space and as the segment grows, extents at the further points in a data file get allocated , which increases the value of ROWID, and as the ROWID is dependent on factors such as FILE_id, blockID, position in the block, data_object_id, the rowid for a particular key value will always increase when the extents are allocated further beyond the current point.

Since the indexed key value is only not incrementing monotonically and have finite distinct values most of the time unless unique, which presents a 90-10 split, which may cause only some unnecessary free space usage, but not the case with 50-50 block splits.

the ROWIDs are sorted in the index. This can pose two different challenges.

if the extents are always allocated beyond the current position which is most likely the case when the datafiles are added with autoextend on, and the ROWIDs increases, the index leaf blocks for a particular key value, will mostly be only half full. unless the extent gets allocated below the current position of existing indexes only then the key value gets inserted to a leaf block or the value that we insert falls in the range. For example, we know that marks can be 1-100 but never fractions, the index leaf blocks are getting reused only when we insert values such as 1.1 or 1.2 which never occur in an application. so the usage is only about 50% in these cases.
Unless the applications delete the entries or update the entries of other rows with ROWIDs that falls within the range captured by a particular index block. if there are mostly inserts and deletes, then the space usage in a leaf block in this scenarios will be even less - as PCTFREE is relevant only during index creation... this may not pose a challenge when the rowsizes are large in which case the clustering factor is almost always high and the number of index blocks visited during an index range scan is very less, but his poses a challenge when the row sizes are small as the index sizes are large. in which case an index range scan ends up performing more consistent gets than required.

This becomes even challenging to answer why the consistent gets for a particular index key value is high or less, as it reads many index blocks. this is when the extents gets allocated below the current position of the extent as the ROWID will be less, in this case the chances of index leaf blocks being used is far good, and this can again pose two challenges, even though the uniform distribution of values, the consistent gets for each value can differ significantly. coalesce and shrink space provides a better alternative in these cases, in consolidating the leaf blocks to get the consistent gets uniform provided uniform distribution of values.

To mitigate this we dont have a parameter that can control how a block split happens, as this gives us more control over redo generation during block splits and space wastage if we know the workload all well, and could be related to balancing the b-tree not sure or it might be more guesstimate looking at the worst case scenario..

i mean coalesce is a better option considering a rebuild as it requires additional space but this prevents index prefetching as the blocks are stored at random locations, and rebuild can help read the index leaf blocks that are adjacent in one go but the saving in terms of IOPS may be less but provided the SQL statements are executed more frequently this poses a challenge with coalesce.

Thanks,
Vishnu

On Wed, Nov 27, 2019 at 4:46 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> L1 BMB are used to track usage for index blocks, but they only record 2
> states: FULL or FS2
>
> If an index (leaf) block has some entries it doesn't matter how much free
> space it has, you can only insert a new entry into it if the entry is
> supposed to be in that leaf block, so any attempt to show how much free
> space is available is a waste of time. The only interesting conditions are
> (a) it's got some entries in it (FULL) or (b) it's got no entries left in
> it and could be re-used somewhere else in the index (FS2).
>
> Note - an index leaf block is left in the index structure even if it's
> completely empty until such time as Oracle needs to add a new leaf block to
> the index, then the block can be detached from its current location and
> reused in a new location. This is also true for freelist management, where
> an index block could be both on a free list and in the structure at the
> same time.
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Vishnu Potukanuma <vishnupotukanuma_at_gmail.com>
> Sent: 27 November 2019 11:03
> To: Oracle L
> Subject: 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
> 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_at_oracle trace]$ alter system dump datafile ^C
> [oracle_at_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
>
>

--
http://www.freelists.org/webpage/oracle-l