As far as sizing and efficiency of indexes is concerned, Oracle doesn't really
help at all - though if they got rid of the exclusive lock during "analyze
index validate structure" that would be a big help.
However it's not difficult to write code that produces help information about
indexes but as you might expect - if you want detail you have to read the index
blocks.
https://jonathanlewis.wordpress.com/index-efficiency-3/ This one tells you ;
about the number of index entries per leaf block, then summarises to showing
how many leaf blocks there are with the given numbers of entries. So an
indication of uneven usage
https://jonathanlewis.wordpress.com/index-sizing/ If your index stats are up to ;
date this tells you how many leaf blocks there are in each index and how many
leaf blocks there would be if you rebuilt it at any given percentage. It's
very old and has lots of limitations - most particularly it doesn't cater for
prefix compression.
https://jonathanlewis.wordpress.com/2009/08/17/treedump/ Tells you how to do a ;
treedump - which gets you the index walked in order and a report of rows per
leaf block in the session's trace file. (Basically a better option than the
index_efficiency_3 query but without the summary).
https://jonathanlewis.wordpress.com/2010/03/07/treedump-2/ Post-processing a ;
treedump by reading a trace file as an external table.
https://jonathanlewis.wordpress.com/2010/07/01/index-branches/ Variant on ;
treedump-2 that reports only branch blocks, allowing irregular deletion
patterns in indexes to become visible.
I'll repeat what I said before about the importance of being aware of potential
issues so that you are open to spotting the threats when you see a working
environment. If you try and anticipate everything that could happen (and don't
test very carefully) you have to think about all the reasons why you might be
wrong, or why you may have missed something, or why the thought might be
irrelevant (or put up with people like me telling you why ... )
Take, for example, your "marks from 1..100" and 50/50 split. If the data is
evenly distributed there are two corollaries:
a) there's no point in having the index at all, it will be quicker to do a
tablescan for any single value than to use the index. The index may (for
example) send you to every 10th block in the table - when a single seek and 1MB
read would get you 128 blocks, which means 12 of the ones you want in single
read.
b) Worrying about the index being twice the size it needs to be will (almost
always) be a minor performance detail because each index block you read might
result in 200 table block reads - so you would be saving maybe 1/4 of one
percent of the total I/O by improving the packing of the index.
Regards
Jonathan Lewis
________________________________________
From: Vishnu Potukanuma <vishnupotukanuma@xxxxxxxxx>
Sent: 27 November 2019 12:24
To: Jonathan Lewis
Cc: Oracle L
Subject: 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@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> 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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Vishnu Potukanuma
<vishnupotukanuma@xxxxxxxxx<mailto:vishnupotukanuma@xxxxxxxxx>>
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@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
--
//www.freelists.org/webpage/oracle-l
