jump to navigation

Curious Case Of The Ever Increasing Index Solution (A Big Hurt) January 5, 2012

Posted by Richard Foote in ASSM, Indexing Myth, Oracle Indexes, Quiz.
6 comments

Based on the excellent comments in the Quiz post, we have some clever cookies out there :)

I guess the first thing to point out is that based in the basic scenario provided, the index shouldn’t ordinarily be continually growing in this fashion. Although the index values are monotonically increasing, the deletions are leaving behind fully emptied leaf blocks which can generally be recycled in future block splits.

OK, so why is this index behaving in this fashion, continually to increase in size while the number of rows in the table remains relatively constant ?

Well, there are a number of contributing factors.

As stated, the index values are indeed monotonically increasing so all inserts into the index are hitting the right-most index leaf block within the index structure and the deletions which all occur on the “left-side” of the index are leaving behind leaf blocks that contain nothing but deleted index entries. As Marcus and David mentioned in the comments, the use of a Reverse Key index would therefore alleviate this problem as subsequent inserts will be performed within the same leaf blocks in the index structure, automatically cleaning out and reusing space used by previously deleted index entries. This solution though may create as many problems as it solves (if say range predicate statements relied on a Non-Reverse index).

Additionally, the processing is being performed with a PL/SQL block. Oracle has a whole lot of smarts to make PL/SQL as efficient as possible and so the manner in which code runs within PL/SQL compared to other native languages can vary significantly. Unfortunately at times, these smarts might not be quite so smart after all.

The tablespace used to store the index is a Locally Managed Tablespace (LMT) with Automatic Segment Storage Management (ASSM). Instead of freelists (or freelist groups), Oracle uses a set of bitmap blocks within the index segment to determine the amount of free space available within its blocks and whether a block is available for inserts. As Vyacheslav and Alberto highlighted in the comments, there are a number of “issues” in the manner in which these bitmap blocks are maintained within PL/SQL processing. This is effectively locking out the vast number of these now effectively empty leaf blocks from being recycled and reconsidered for subsequent inserts. By rebuilding the index once in a Manual Segment Space Management (MSSM) tablespace would also alleviate this issue.

The actual processing involved within the PL/SQL block can also have an impact. The procedure contained the following important statement:

select min(id),max(id) into n,m from bowie;

By removing this statement from the PL/SQL block and either manually defining the values to be processed or passing them through to the procedure, can impact how PL/SQL manages the freespace bitmaps within the segment. For example, if one used something similar to the following:

SQL> declare
  2      n number:= 1;
  3      m number:= 200000;
  4  begin
  5      for i in 1..200000 loop
  6          delete from bowie where id=n+i-1;
  7          insert into bowie values(m+i,'David Bowie');
  8          if (i mod 1000=0) then
  9            commit;
 10          end if;
 11      end loop;
 12      commit;
 13  end;
 14  /

The number of leaf blocks allocated will be nowhere as significant as before and stabilised after a few runs to approximate:

SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME             HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------ ---------- ---------- ----------- ---------- ----------
BOWIE_PK              3     289040       89040        744          4

Finally, the PL/SQL procedure only performed a Commit after 1000 iterations. This means that there were 1000 deletions performed during a logical transaction. As Greg mentioned in the comments, Unique Key index values can be reused within a single transaction BUT only if the same actual values are reused. The procedure introduces new values for those that have been deleted and so the deleted entries can’t be reused during the same transaction. This means there will be at least 1000 deleted index entries that can’t be reused during the running of the procedure and sufficient additional leaf blocks to accommodate these 1000 index entries will need to be allocated, even if we use some of the solutions mentioned, such as Reverse Key indexes or MSSM tablespaces. By performing either all the necessary deletions within one transaction followed by the inserts or having a Commit for each delete/insert pairing, these additional blocks won’t be required. For example:

SQL> declare
  2        n number:= 1;
  3        m number:= 200000;
  4    begin
  5      for i in 1..200000 loop
  6           delete from bowie where id=n+i-1;
  7           commit;
  8           insert into bowie values(m+i,'David Bowie');
  9           commit;
 10      end loop;
 11  end;
 12  /

Although of course, the inefficiencies in the processing or the potential breaking of business rules may not make the index savings worthwhile.

So in summary, there a number of things we could do to fix this scenario, rather than simply periodically rebuilding the index all the time. Depending on applicability, we could convert the index to a Reverse Key index (or say Hash Partition), we could move the index to a MSSM tablespace, we could modify our procedure logic to remove the reference to fetching MIN/MAX values, not use PL/SQL, or to make the index as efficient as possible, change the transactional logic to not perform large numbers of inserts and deletes within the same transaction.

So there’s quite a lot happening within what on the surface looks like a simple piece of PL/SQL :)

Curious Case Of The Ever Increasing Index Quiz (She’ll Drive The Big Car) January 4, 2012

Posted by Richard Foote in Index Internals, Indexing Myth, Oracle Indexes, Quiz.
22 comments

I received an email recently that had a nice example of what can potentially go wrong with an index.

Let’s first create a simple table with a unique index and populate it with 200,000 rows (following demo run on 11.2.0.1):

SQL> create table bowie (id number constraint bowie_pk primary key, name varchar2(100));

Table created.

SQL> insert into bowie select rownum, 'BOWIE' from dual connect by level <= 200000;

200000 rows created.

SQL> commit;

Commit complete.

SQL> analyze index bowie_pk validate structure;

Index analyzed.

SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME             HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------ ---------- ---------- ----------- ---------- ----------
BOWIE_PK              2     200000           0        374          1

So far, everything is as expected. With have an index with 200,000 rows that currently has 374 leaf blocks.

OK, what we want to do is basically gradually delete the current set of rows and replace them with 200,000 new rows, with ever-increasing Primary Key values. To this end, we create the following procedure:

SQL> create or replace procedure delete_insert_rows
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from bowie;
  7       for i in 1..200000 loop
  8           delete from bowie where id=n+i-1;
  9           insert into bowie values(m+i,'David Bowie');
 10           if (i mod 1000=0) then
 11                commit;
 12           end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

So the procedure basically determines the current MIN and MAX values of our PK column and gradually deletes the current rows and then inserts new ones. Every 1000 iterations, we commit the changes. Nothing too complex here.

When we run this procedure for the first time:

SQL> exec delete_insert_rows

PL/SQL procedure successfully completed.

SQL> analyze index bowie_pk validate structure;

Index analyzed.

SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME             HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------ ---------- ---------- ----------- ---------- ----------
BOWIE_PK              2     293820       93820        619          1

We notice we now have a whole bunch of deleted leaf entries and that the index has grown from 374 to 619 leaf blocks.

If we run the procedure again:

SQL> exec delete_insert_rows

PL/SQL procedure successfully completed.

SQL> analyze index bowie_pk validate structure;

Index analyzed.

SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME             HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------ ---------- ---------- ----------- ---------- ----------
BOWIE_PK              3     347841      147841        994          3

Things have gone even worse. We still only have 200,000 rows in the table but the index now has an additional 147,841 deleted entries and the number of leaf blocks has again increased substantially to 994 leaf blocks.

If we have a look at a partial treedump of the index:

SQL> select object_id from dba_objects where object_name = 'BOWIE_PK';

 OBJECT_ID
----------
     74060

SQL> alter session set events 'immediate trace name treedump level 74060';

Session altered.

—– begin tree dump
branch: 0x100378b 16791435 (0: nrow: 2, level: 2)
   branch: 0x1003ce0 16792800 (-1: nrow: 733, level: 1)
      leaf: 0x100378e 16791438 (-1: nrow: 149 rrow: 0)
      leaf: 0x100378f 16791439 (0: nrow: 571 rrow: 0)
      leaf: 0x100378c 16791436 (1: nrow: 291 rrow: 0)
      leaf: 0×1003795 16791445 (2: nrow: 571 rrow: 0)
      leaf: 0×1003796 16791446 (3: nrow: 433 rrow: 0)
      leaf: 0×1003797 16791447 (4: nrow: 4 rrow: 0)
      leaf: 0×1003790 16791440 (5: nrow: 571 rrow: 0)
      leaf: 0×1003791 16791441 (6: nrow: 146 rrow: 0)
      leaf: 0×1003792 16791442 (7: nrow: 571 rrow: 0)
      leaf: 0×1003793 16791443 (8: nrow: 288 rrow: 0)
      leaf: 0×1003794 16791444 (9: nrow: 571 rrow: 0)
      leaf: 0x10037a9 16791465 (10: nrow: 430 rrow: 0)

… (most of the treedump has been cut out, following is the last portion of the dump)

  
     leaf: 0x1003e70 16793200 (248: nrow: 533 rrow: 533)
      leaf: 0x1003e74 16793204 (249: nrow: 533 rrow: 533)
      leaf: 0x1003e78 16793208 (250: nrow: 533 rrow: 533)
      leaf: 0x1003e7c 16793212 (251: nrow: 533 rrow: 533)
      leaf: 0x1003e41 16793153 (252: nrow: 533 rrow: 533)
      leaf: 0x1003e45 16793157 (253: nrow: 533 rrow: 533)
      leaf: 0x1003e49 16793161 (254: nrow: 533 rrow: 533)
      leaf: 0x1003e4d 16793165 (255: nrow: 533 rrow: 533)
      leaf: 0x1003e51 16793169 (256: nrow: 533 rrow: 533)
      leaf: 0x1003e3e 16793150 (257: nrow: 533 rrow: 533)
      leaf: 0x1003e03 16793091 (258: nrow: 533 rrow: 533)
      leaf: 0x1003e07 16793095 (259: nrow: 236 rrow: 236)
—– end tree dump

We notice that the first portion of the index contains leaf blocks with nothing but deleted index entries. The number of rrows is 0 for a vast number of leaf blocks. We also notice that the root block has a rba of 0x100378b 16791435, which is only a few values below some of the rba values of the left most indexes in the index structure (say) 0x100378e 16791438. Therefore, this highlights that even though these left most blocks in the index structure contain nothing but deleted index entries, Oracle is not recycling them as it should do. Oracle is simply adding new blocks to the index structure rather than recycling empty leaf blocks, resulting in the index growing bigger and bigger.

The leaf blocks however at the right most end of the index structure (the second portion of the partial treedump), shows us a nice compact set of leaf blocks with lots of index entries per block (most with 533 per leaf block) and with no deleted index entries (rrows matches the nrows value). 

If we run the procedure 10 times in total, we get an index that looks like the following:

SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME             HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------ ---------- ---------- ----------- ---------- ----------
BOWIE_PK              3    1325132     1125132       4136          7

We now have 1,125,132 deleted index entries and the index is now over 10 times the original size, up from 374 to a massive 4,136 leaf blocks, even though the table only contains 200,000 rows.

There are a number of contributing factors here :)

The question is why, why is the index behaving in this fashion and what can we do to ensure the index doesn’t grow in this manner and can remain basically the same size as we delete and insert new rows into the table ?

Rebuilding Indexes and the Clustering Factor Solution (Move On) September 25, 2011

Posted by Richard Foote in Clustering Factor, Index Rebuild, Indexing Myth, Oracle Indexes, Quiz, Reverse Key Indexes.
1 comment so far

Excellent !! This quiz created quite a bit of debate and it was nice to sit back and read some interesting discussions.

The Clustering Factor is basically the measurement of how well aligned the data in the underlining table is in relation to the index and is the number of table related I/Os required to read the entire table via a full index scan. A value of the CF approaching the number of blocks in the table suggests the data is reasonably well sorted/clustered in relation to the index (although the CF could in theory be somewhat less than the blocks in the table of course). A value of the CF approaching the number of index entries suggests the data is not particularly well sorted/clustered in relation to the index and means we may need to re-visit the same table block numerous times to get the required data, thus decreasing the efficiency of using the index, increasing the costs associated with using the index and therefore decreasing the likelihood of the CBO using the index.

So for an index rebuild to actual have an impact on the CF on an index, means either the rows in the table needs to change or the order of the index entries needs to change.

However, when we typically rebuild an index, it has no impact at all on the table and so can’t possibly change the order of the rows there. Additionally, no matter how fragmented or inefficient the index structure might be, an index rebuild doesn’t change the order of the index entries either as they’re always sorted within the index in the order of the indexed columns.

Therefore an index rebuild typically has no impact at all on the CF of an index, no matter the current value of the CF.

However, there is an exception to this rule.

If we rebuild the index and change it from a NOREVERSE index to a REVERSE index, we now do change the order of the index. Significantly so, as the index entries are now in the order of the index column values when reversed. Therefore this can in turn significantly change the CF of an index.

Conversely, if we rebuild an index and change it from REVERSE to NOREVERSE, we likewise significantly change the order of the index entries and hence the value of the CF.

For a nice little demo, see David Aldridge’s comment or my previous discussion on Reverse Key Indexes.

Of course, it’s always nice to see new ideas and something I hadn’t considered was Gary Myer’s comment regarding changing the logic behind a Function-Based Index prior to a rebuild …

So the moral of this story is that no matter how poorly fragmented the index, how high or low the current CF of an index might be, rebuilding an index in order to improve the CF is a futile exercise and will change less than diddly-squat, except in the above mentioned special circumstances.

Now, back to another hearing of Pink Floyd’s masterpiece “The Dark Side of the Moon” in all its surround sound glory :)

DEL_LF_ROWS Index Rebuild Criteria ? (Codex) May 22, 2011

Posted by Richard Foote in DEL_LF_ROWS, Indexing Myth, Oracle Indexes.
19 comments

In the same article and book where the erroneous claims regarding the BLKS_GETS_PER_ACCESS were made, we also find the following recommendation:
 
Another rebuild condition would be cases where deleted leaf nodes comprise more than 20% of the index nodes“.
 
This is a very common claim however no matter how often it might be stated, it’s fundamentally flawed. Note My Oracle Support previously made these claims as well, such as with Metalink note 122008.1, although this has now been totally revised with the recommendation withdrawn.
 
It’s a flawed index rebuild criteria for two very good reasons.

1) It assumes this deleted space is somehow “wasted” or “deadwood” and needs to be removed. However, in the majority of scenarios, it’s nothing more than free space which will be subsequently reused by later inserts into the index. Therefore, basing a rebuild criteria on the percentage of deleted space will likely include indexes that will not benefit from a rebuild.

2) It assumes the DEL_LF_ROWS index statistic somehow accounts for all the “deleted” space within an index. However this statistic can significantly under-estimate the potential “wasted” space within an index. Therefore basing a rebuild criteria on the percentage of  “currently marked” deleted space can possibly miss indexes that might actually benefit from a rebuild (or coalesce).
 
Any criteria which selects indexes that don’t need to be rebuilt but misses out on those that do, is as I say, fundamentally flawed. Basing a rebuild criteria on the percentage of currently marked deleted index entries suggests a lack of understanding of both how Oracle indexes work and the actual meaning behind DEL_LF_BLKS.
 
This of course has been discussed many times before, but a quick demo to illustrate. First, I’m going to create a table with 10 rows with a unique ID column.

SQL> create table bowie (id number, code number, name varchar2(30));
 
Table created.
 
SQL> insert into bowie select rownum, rownum, 'ZIGGY STARDUST' from dual connect by level <=10;
 
10 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> create index bowie_id_i on bowie(id);
 
Index created.

 

If we now delete four of these rows:


SQL> delete bowie where id in (2,4,6,8);
 
4 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index bowie_id_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_delete from index_stats;
 
LF_ROWS DEL_LF_ROWS PCT_DELETE
------- ----------- ----------
     10           4         40

 
Indeed, we currently have 4 index entries marked as deleted. If we look at a dump of this index block:
 

Block header dump:  0x01c0521b
 Object id on Block? Y
 seg/obj: 0×13039  csc: 0×00.e30e53  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1c05218 ver: 0×01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
0×02   0×0005.017.00004219  0x00c002b4.0a36.07  –U-    4  fsc 0×0038.00e30e79
Leaf block dump
===============
header address 220340836=0xd222264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 10
kdxcofbo 56=0×38
kdxcofeo 7916=0x1eec
kdxcoavs 7860
kdxlespl 0
kdxlende 4
kdxlenxt 0=0×0
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  01 c0 52 13 00 00
row#1[8012] flag: —D–, lock: 2, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 c0 52 13 00 01
row#2[8000] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  01 c0 52 13 00 02
row#3[7988] flag: —D–, lock: 2, len=12
col 0; len 2; (2):  c1 05
col 1; len 6; (6):  01 c0 52 13 00 03
row#4[7976] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 06
col 1; len 6; (6):  01 c0 52 13 00 04
row#5[7964] flag: —D–, lock: 2, len=12
col 0; len 2; (2):  c1 07
col 1; len 6; (6):  01 c0 52 13 00 05
row#6[7952] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 08
col 1; len 6; (6):  01 c0 52 13 00 06
row#7[7940] flag: —D–, lock: 2, len=12
col 0; len 2; (2):  c1 09
col 1; len 6; (6):  01 c0 52 13 00 07
row#8[7928] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 0a
col 1; len 6; (6):  01 c0 52 13 00 08
row#9[7916] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 0b
col 1; len 6; (6):  01 c0 52 13 00 09
—– end of leaf block dump —–
 

We note there are currently 4 rows with the delete flag set (eg. row#1[8012] flag: —D–). In order to make the transaction more efficient, Oracle doesn’t physically remove the deleted index entries but marks them as logically deleted. However, any subsequent DML within the index block will result in all the deleted entries being physically removed from the leaf block.

For example, here we insert a new index entry with a value of 42. Note this value didn’t exist previously and is outside all the current values within the index leaf block:

  
SQL> insert into bowie values (42, 1, 'MAJOR TOM');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index bowie_id_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_delete from index_stats;
 
   LF_ROWS DEL_LF_ROWS PCT_DELETE
---------- ----------- ----------
         7           0          0

 

Note that this has resulted in the removal of the previously logically deleted index entries. There are currently no deleted index entries within the leaf block. A block dump now clearly shows the deleted index entries are now all gone:

 
 
Block header dump:  0x01c0521b
 Object id on Block? Y
 seg/obj: 0×13039  csc: 0×00.e31029  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1c05218 ver: 0×01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
0×02   0×0005.012.00004213  0x00c002b4.0a36.31  –U-    1  fsc 0×0000.00e3102b
Leaf block dump
===============
header address 220340836=0xd222264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 7
kdxcofbo 50=0×32
kdxcofeo 7904=0x1ee0
kdxcoavs 7902
kdxlespl 0
kdxlende 0
kdxlenxt 0=0×0
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  01 c0 52 13 00 00
row#1[8000] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  01 c0 52 13 00 02
row#2[7976] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 06
col 1; len 6; (6):  01 c0 52 13 00 04
row#3[7952] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 08
col 1; len 6; (6):  01 c0 52 13 00 06
row#4[7928] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 0a
col 1; len 6; (6):  01 c0 52 13 00 08
row#5[7916] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 0b
col 1; len 6; (6):  01 c0 52 13 00 09
row#6[7904] flag: ——, lock: 2, len=12
col 0; len 2; (2):  c1 2b
col 1; len 6; (6):  01 c0 52 15 00 00
—– end of leaf block dump —–
 

So deleted space in most scenarios is nothing more than free space that can be cleaned out and reused by subsequent DML operations.
 
Lets look at another example, this time on a larger table with 1M rows with two indexes. The first index is effectively unique while the second index has 100 evenly distributed values (and so 10000 occurrences of each value):
 

SQL> create table radiohead (id number, code number, name varchar2(30));
 
Table created.
 
SQL> insert into radiohead select rownum, mod(rownum, 100), 'OK COMPUTER' from dual connect by level <= 1000000;
 
1000000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> create index radiohead_code_i on radiohead(code);
 
Index created.
 
SQL> create index radiohead_id_i on radiohead(id);
 
Index created.

 

If we now delete 40% of the table, those with an ID up to 400000, this has 2 different effects on our indexes. On the ID index, it will result in the first 40% of leaf blocks containing nothing but deleted index entries. On the CODE index, it will result in all the leaf blocks containing approximately 40% of deleted index entries. Note this delete is being performed within a single very large logical transaction, not via lots of small discrete transactions, so that none the deleted entries are cleaned out. Note this also requires a sufficiently large buffer cache to cache all this data to prevent read operations (including the validate structure command itself) from subsequently cleaning out the deleted index entries as well via subsequent block cleanout operations.
 

SQL> delete radiohead where id between 1 and 400000;
 
400000 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index radiohead_code_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats;
 
 LF_ROWS DEL_LF_ROWS PCT_DELETED
-------- ----------- -----------
 1000000      400000          40
 
SQL> analyze index radiohead_id_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats;
 
 LF_ROWS DEL_LF_ROWS PCT_DELETED
-------- ----------- -----------
 1000000      400000          40

 

So both have a “large” percentage of deleted index entries, although the distribution of such deleted entries differs between the two indexes. A rebuild criteria simply based on the percentage of deleted entries would suggest both indexes need a rebuild. However, if we were to re-insert a similar amount of data again (note, even with a new range of values for the ID column):
 

SQL> insert into radiohead select rownum+1000000, mod(rownum,100), 'THE KING OF LIMBS' from dual connect by level <= 400000;
 
400000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index radiohead_code_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats;
 
 LF_ROWS DEL_LF_ROWS PCT_DELETED
-------- ----------- -----------
 1000002           2       .0002

SQL> analyze index radiohead_id_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats;
 
 LF_ROWS DEL_LF_ROWS PCT_DELETED
-------- ----------- -----------
 1007633        7633  .757517866
 

We notice that the vast majority of the deleted entries have automatically been cleaned out, without an index rebuild in sight. In the case of the ID index with 40% of index leaf blocks containing nothing but deleted index entries, even though a different range of values were inserted, the effectively “empty” leaf blocks were recycled within the index, removing all the associated deleted index entries in the process.
 
In the case of the CODE index with all the leaf blocks containing approximately 40% of deleted index entries, subsequent inserts into these leaf blocks cleaned out all the deleted index entries and reused the subsequently freed space.
  
Once you begin to understand how Oracle reuses deleted index space and how the free space related to previously deleted entries may not actually be reflected in the DEL_LF_ROWS statistic anyways, one begins to understand why basing an index rebuild criteria on the so-called ratio of deleted index entries is so flawed.

In fact, if you want to avoid some nonsensical  index rebuild criteria based on DEL_LF_ROWS, all you need to do is simply delete yet more rows, as so wonderfully here explained by Jonathan Lewis.

To illustrate, we create a table and index and populate it with 100000 rows:

SQL> create table pink_floyd (id number, code number, name varchar2(30));
 
Table created.
 
SQL> insert into pink_floyd select rownum, mod(rownum, 100), 'WISH YOU WERE HERE' from dual connect by level <= 100000;
 
100000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> create index pink_floyd_id_i on pink_floyd(id);
 
Index created.
 
SQL> analyze index pink_floyd_id_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats;
 
 LF_ROWS DEL_LF_ROWS PCT_DELETED
-------- ----------- -----------
  100000           0           0

 

So far, so good. Let’s now deleted 40% of the rows throughout the table:


SQL> delete pink_floyd where mod(id,100) between 20 and 29 or mod(id, 100) between 40 and 49 or mod(id, 100) between 60 and 69 or mod(id, 100) between 80 and 89;
 
40000 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index pink_floyd_id_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats;
 
 LF_ROWS DEL_LF_ROWS PCT_DELETED
-------- ----------- -----------
  100000       40000          40
 

Oh dear, we have an index with 40% of the rows deleted. However, rather than rebuild the index, let’s simply delete a few more rows …


SQL> delete pink_floyd where mod(id, 100) = 1;
 
1000 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index pink_floyd_id_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats;
 
 LF_ROWS DEL_LF_ROWS PCT_DELETED
-------- ----------- -----------
   60000        1000  1.66666667

What do you know, now the ratio of deleted rows is just 1.67%, no need to rebuild it now !!

If you have a rebuild criteria which selects indexes that don’t actually need to be rebuilt and it misses out on those that potentially could benefit from a rebuild, while at the same time makes indexes look less in need of a rebuild the more you actually potentially fragment the index with further deletions, well as I said at the start, such a rebuild criteria is fundamentally flawed …

BLKS_GETS_PER_ACCESS Index Rebuild Criteria ? (Twisted Logic) April 20, 2011

Posted by Richard Foote in Indexing Myth, Oracle Indexes, Validate Structure.
17 comments

A recent question on the database OTN forum and a previous request by Charles Hooper that I cover some basic indexing concepts for newbie’s who might be confused by “dubious” information out there in internet land has prompted me to discuss the BLKS_GETS_PER_ACCESS metric, available in INDEX_STATS after an analyze validate structure operation.
 
The OTN thread had someone questioning why after freshly rebuilding an index, the index still had a particularly “high” BLKS_GETS_PER_ACCESS value of 110 and wouldn’t reduce down below a value of 5. They requested if someone could please explain why the BLKS_GETS_PER_ACCESS was not getting reduced, as they wanted it to be below 5.
 
The two obvious questions I had in return were why of earth would anyone want the BLKS_GETS_PER_ACCESS to be below 5 and why on earth would they think rebuilding the index would reduce the BLKS_GETS_PER_ACCESS from a value of 110 down to 5.
 
However a quick Google search confirmed my suspicions, subsequently confirmed by the OP. This “Identifying Which Indexes to Rebuild” article by Don Burleson states:
 
Gets per index access
 
The number of “gets” per access refers to the amount of logical I/O that is required to fetch a row with the index. As you may know, a logical “get” is not necessarily a physical I/O since much of the index may reside in the Oracle buffer cache. However, any SAP index with a number greater than 10 would probably benefit from an index rebuild.”
 
and
 
We might want to rebuild an index if the “block gets” per access is greater than five, since excessive “blocks gets” indicate a fragmented b-tree structure.”
 
The same claims are made on page 727 in “Oracle Tuning: The Definitive Reference” .
 
The problem with all this of course is that it’s a complete nonsense. The very idea of having a rebuild criteria based on BLKS_GETS_PER_ACCESS being greater than value “X” and that a subsequent index rebuild will reduce BLKS_GETS_PER_ACCESS down to less than value “X” shows a complete lack of understanding of what BLKS_GETS_PER_ACCESS actually represents.
 
BLKS_GETS_PER_ACCESS is basically the number of blocks required to get a randomly chosen row of interest via an index.
 
The Oracle Reference Manual describes BLKS_GETS_PER_ACCESS somewhat ambiguously as:

Expected number of consistent mode block reads per row, assuming that a randomly chosen row is accessed using the index. Used to calculate the number of consistent reads that will occur during an index scan.”
 
The key point here is that it’s a “randomly” chosen row when accessed via the specific index.
 
Now in the case of a Unique index, the number of blocks needed to access a random row can easily be determined as simply being the height of the index plus one additional block to access the associated table block. If the index is Unique, there can only be one row per index value which requires precisely one visit to the table.
 
In the following example, we create a table with 1M rows with two indexes. The index on the ID column is effectively unique as there are 1M distinct values while the index on the CODE column is Non-Unique with only 100 distinct values and so with 10000 occurrences of each indexed value.

  
SQL> create table bowie (id number, code number, name varchar2(30));
 
Table created.
 
SQL> insert into bowie select rownum, mod(rownum,100), 'ZIGGY STARDUST' from dual connect by level <= 1000000;
 
1000000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> create index bowie_id_i on bowie(id);
 
Index created.
 
SQL> create index bowie_code_i on bowie(code);
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', cascade=>true, estimate_percent=>null, method_opt=> 'FOR ALL COLUMNS SIZE 1');
 
PL/SQL procedure successfully completed.
 
SQL> select index_name, num_rows, distinct_keys from dba_indexes where table_name='BOWIE';
 
INDEX_NAME     NUM_ROWS DISTINCT_KEYS
------------ ---------- -------------
BOWIE_CODE_I    1000000           100
BOWIE_ID_I      1000000       1000000

 
 

If we now collect INDEX_STATS on the effectively Unique index:

  
SQL> analyze index bowie_id_i validate structure;
 
Index analyzed.
 
SQL> select height, lf_rows, distinct_keys, rows_per_key, blks_gets_per_access from index_stats;
 
HEIGHT    LF_ROWS DISTINCT_KEYS ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------ ---------- ------------- ------------ --------------------
     3    1000000       1000000            1                    4
 

 
 

We can see that the BLKS_GETS_PER_ACCESS is 4 as expected. 3 due to the index height and additional 1 to access the specific row in the associated table block.
 
The formula Oracle uses here is basically:

HEIGHT + (LF_ROWS/DISTINCT_KEYS + 1)/2 = HEIGHT + (ROWS_PER_KEY + 1)/2 = 3 + (1 + 1)/2 = 3 + 2/2 = 3 + 1 = 4.
 
So basically to calculate the BLKS_GETS_PER_ACCESS, we simply take the ROWS_PER_KEY, add 1 to it, then divide the total by 2 and finally add the index height to get the final value. The reason for this exact formula makes more sense when we look at a Non-Unique index.
 
How do we cost the access to a specific row via a non-unique index when there could be multiple occurrences of the specific index value ?
If there are say 100 occurrences of an indexed value, if we want the first of these within the index, then we need to access the same blocks as per the unique index (index height plus 1). However, if we want the last of these 100 occurrences referenced within the index, then we might need to access index height + the 100 blocks until we reach the last occurrence of the indexed value. If we’re simply interested in an “average” row, then on average we might need to access 1/2 the ROWS_PER_KEY in addition to the index height.

So the formula is now basically HEIGHT + ROWS_PER_KEY/2. But as this is only an average guesstimate to begin with and so as we don’t ruin the perfect value we can derive for Unique Indexes, the formula is adjusted a tad by adding 1 to the ROWS_PER_KEY/2 figure so that the result makes sense and is accurate for Unique indexes as well.
 
Hence the final formula of HEIGHT + (ROWS_PER_KEY + 1)/2.
 
If we now look at the index on the CODE column, which has only 100 distinct values (and so 10000 occurrences per distinct value):

 

SQL> analyze index bowie_code_i validate structure;
 
Index analyzed.
 
SQL> select height, lf_rows, distinct_keys, rows_per_key, blks_gets_per_access from index_stats;
 
HEIGHT    LF_ROWS DISTINCT_KEYS ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------ ---------- ------------- ------------ --------------------
     3    1000000           100        10000               5003.5

 
 

HEIGHT + (ROWS_PER_KEY + 1)/2 = 3 + (10000 + 1)/2 = 3 + 5000.5 = 5003.5.
 
We can see how this 5003.5 figure is actually derived.
 
The actual blocks needed to be accessed when using the index is of course also very highly dependant on the Clustering Factor (CF) of the index but the CF is not calculated as part of Index_stats. The BLKS_GETS_PER_KEY can therefore be viewed as being a guesstimate of the number of blocks required to read a specific indexed value, based on an “average” CF. As the maximum CF is LF_ROWS, an “average” CF can therefore be viewed as simply being 1/2 of LF_ROWS.
 
In the above example, an “average” CF would therefore be 1000000/2 = 500000.
 
To access all table blocks for a specific indexed value would therefore basically be:

500000/distinct keys = 500000/100 = 5000.

If we then add the index height = 5000 + 3 = 5003.
 
5003 is almost identical to 5003.5, the 0.5 difference simply attributed to the additional 1 that’s added in the previous formula.
 
So BLKS_GETS_PER_ACCESS can effectively be viewed as being the either the number of blocks required to access a specific row “on average” within the table or the number of blocks required to read all occurrences of a specific index value IF the index had an average CF.
 
Note that both definitions are nothing but wild guesstimates of the blocks that might actually need to be accessed as both are making very broad assumptions in that the CF is indeed “average”, that the accessed row of interest is indeed “somewhere near the middle” of the index range scan, that the data is indeed evenly distributed, etc. etc. The actual blocks that might need to be accessed when using the index could therefore be significantly different if these basic assumptions are not correct.
 
Now here come a few interesting points.
 
Firstly, note the formula used only takes into consideration the index height and the average expected accesses to the table. The possible accesses to additional index leaf blocks is not considered at all.
 
Why ?
 
Likely because the vast majority of accesses involving an index range scan actually involves the block reads associated with accessing the table, not reading the index itself. As the figure is only a very rough estimate to begin with, it’s somewhat pointless adding a little here or there for the trivial additional leaf blocks that might need to be accessed during a scan . So in order to keep things simple, only the index height is considered in the actual BLKS_GETS_PER_ACCESS costings.
 
Therefore, an index rebuild is likewise going to have a trivial impact on the derived BLKS_GETS_PER_ACCESS as only the index height is considered in its calculation. In the vast majority of cases, rebuilding an index will have absolutely no impact at all as most index heights are not impacted by an index rebuild. In extremely rare occasions, an index might reduce its height but then the final BLKS_GETS_PER_INDEX is only going to reduced by 1. The absolute maximum amount that an index rebuild can impact the BLKS_GETS_PER_ACCESS is just HEIGHT-1.
 
In the above example, the BLKS_GETS_PER_ACCESS is 5003.5, substantially greater than 5 or 10 or even 42. However, rebuilding the index:

  
SQL> alter index bowie_code_i rebuild;
 
Index altered.
 
SQL> analyze index bowie_code_i validate structure;
 
Index analyzed.
 
SQL> select height, lf_rows, distinct_keys, rows_per_key, blks_gets_per_access from index_stats;
 
HEIGHT    LF_ROWS DISTINCT_KEYS ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------ ---------- ------------- ------------ --------------------
     3    1000000           100        10000               5003.5

 

  
makes precisely no difference at all.
 
So what do we do, just keep rebuilding this same index week after week after week as it continually meets the ill-considered index rebuild guidelines …
 
Note also that the actual value of BLKS_GETS_PER_ACCESS could be anything, as it’s primarily based on the number of rows per keys. For a very very large table on an index with relatively few distinct values, this figure could likewise be “very large”, even with the most perfectly compact, defragmented index.
 
Therefore, having an index rebuild criteria based if some nominal value of BLKS_GETS_PER_ACCESS “is excessive” (be it 5 or 10 or 42 or 5000 or whatever) is simply nonsensical as this value has practically nothing to do with the efficiency of an index but is directly proportional to the average number of rows per index key.

Additionally, suggesting an index rebuild will have a significant impact on BLKS_GETS_PER_ACCESS is likewise nonsensical as the only cost directly attributed to the index itself is the index height, which very rarely changes following an index rebuild. In fact, the BLKS_GETS_PER_ACCESS formula specifically negates the impact of any potential index rebuilds by implicitly excluding index block leafs in its calculations.
 
In short, basing an index rebuild criteria on the value of BLKS_GETS_PER_ACCESS is totally ludicrous. It’s actually similar to the silly myth that one should rebuild an index if the clustering factor is “too large”, where in actual fact there is no such threshold value and an index rebuild doesn’t impact the subsequent CF anyways.
 
I hate to think how many indexes have been repeatedly rebuilt unnecessarily based on a rebuild criteria where BLKS_GETS_PER_ACCESS is greater than value “X”, when such an index rebuild has made absolutely no difference to the original rebuild criteria :(

Follow

Get every new post delivered to your Inbox.

Join 1,818 other followers