Oracle Tips and Tricks — David Fitzjarrell

January 13, 2012

My, How You’ve Grown

Filed under: Indexes — dfitzjarrell @ 19:21
Tags: , ,

Indexes are interesting objects — they can dramatically improve performance but their management can be, well, tricky. Depending upon how data is inserted into and deleted from a table the size an index can attain could be surprising to the DBA. How can the size be surprising? Let’s take an example through a number of iterations and see what Oracle does with the index, and explain why the results shouldn’t be unexpected.

Setting the stage we’ll create a table and a primary key index, load 200000 rows, delete the existing rows and insert new keys then see how the index responds. We’ll do this several times, under differing conditions, to see if the behaviour changes and, if so, why. By the end of the example we should know how index leaf blocks are used and re-used and why some dead space can remain in an index even though general wisdom says otherwise. We begin:

  
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> --
SQL> 
SQL> create table biggy (id number constraint biggy_pk primary key, name varchar2(100));

Table created.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     245378       45378        495          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             45378       200000   18.49310

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     293295       93295        587          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             93295       200000   31.80927

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69706

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                3     484820      284820       1011          5

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                            284820       200000   58.74758

We find that the way we’ve deleted and added the rows has affected how the leaf blocks are managed using a standard primary key index as the index has more than doubled in size; a good portion of that storage is empty leaf blocks that could not be reused by the new keys because they are out of the key range for the associated branch block. Let’s reverse the index and try this exercise again:

  
SQL> 
SQL> --
SQL> -- Drop the existing table and index then recreate
SQL> -- 
SQL> 
SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> select count(*), min(id), max(id) from biggy;

  COUNT(*)    MIN(ID)    MAX(ID)
---------- ---------- ----------
    200000     400001     600000

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205095        5095        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5095       200000    2.48421

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69709

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470

SQL> 

The reverse-key index made a substantial impact on the number of empty leaf blocks left unused in the index. Let’t see if manual segment space management causes a change in behaviour over the prior tests using ASSM:

  
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> -- Use a tablespace with manual segment management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in when ASSM is used
SQL> --
SQL> -- MSSM may eliminate the behaviour
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     201000        1000        379          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              1000       200000     .49751

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202000        2000        382          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2000       200000     .99010

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69711

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210000       10000        422          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10000       200000    4.76190

SQL> 

We note that manual segment space management didn’t really make much of a difference in the storage with the standard primary key index; the index is still much larger than it ‘should’ be. Will it make a difference with the reverse-key version? Let’s test it and see:

  

SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> -- Again we use a tablespace with manual extent management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205095        5095        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5095       200000    2.48421

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69714

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470

SQL> 

No real difference seen with the reverse-key index so the segment space management (at least in 11gR2) isn’t a factor. One thought on mitigating this behaviour is to set session_cached_cursors to 0; let’s see what that does:

  
SQL> 
SQL> --
SQL> -- Set session_cached_cursors to 0
SQL> --
SQL> -- May improve the situation further
SQL> --
SQL> 
SQL> alter session set session_cached_cursors = 0;

Session altered.

SQL> 
SQL> --
SQL> -- Go through the whole process one more time
SQL> -- with and without a reverse-key index
SQL> -- and using ASSM then MSSM to see which is better
SQL> --
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> --
SQL> 
SQL> create table biggy (id number constraint biggy_pk primary key, name varchar2(100));

Table created.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     225031       25031        440          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             25031       200000   11.12336

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     228120       28120        446          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             28120       200000   12.32685

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69716

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     237132       37132        495          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             37132       200000   15.65879

SQL> 

Nothing changed for the standard index; let’s again test the reverse-key index and see what that produces:

  

SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205095        5095        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5095       200000    2.48421

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69718

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470

SQL> 

Again no change is realized; we try again with manual segment space management and collect the results:

  
SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> -- Use a tablespace with manual segment management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in when ASSM is used
SQL> --
SQL> -- MSSM may eliminate the behaviour
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     201000        1000        379          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              1000       200000     .49751

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202000        2000        382          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2000       200000     .99010

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69720

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210000       10000        422          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10000       200000    4.76190

SQL> 

Again, no change. One more time with the reverse-key index, using manual segment space management:

  

SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> -- Again we use a tablespace with manual extent management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205061        5061        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5061       200000    2.46805

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69722

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
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
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470SQL> 

Notice the setting didn’t do much of anything to improve the situation.

So, what happened? The initial pass, with the standard index, causes Oracle to wait to reuse empty leaf blocks until the branch block they are attached to is empty; since we deleted the smallest available key then inserted a new largest key the leaf block released could not immediately be reused as the branch block still had leaf blocks attached to it. Somewhere around the middle of the whole delete/insert process the leaf blocks we released at the beginning of the process were finally available for reuse. Reversing the key on the primary key index allowed reuse of the leaf blocks by the new keys since, in reverse order, they could ‘fit in’ to the key order of the index. Manual segment space management didn’t do much to improve this nor did setting session_cached_cursors to 0.

Of course the ideal method is to delete the rows in batches with the intent of freeing the branch block so the empty leaf blocks can be reused but piecemeal deletes and inserts can and will happen in OLTP systems so such a scenario can be repeated in a running production database. An interesting side note on this is that primary key indexes aren’t usually rebuilt as reverse-key indexes unless block contention is high for the index yet that action can also dramatically reduce the number of empty leaf blocks in the index after rows are deleted. It may be worth considering the use of a reverse-key primary key index to keep the index size ‘reasonable’.

It may be a rare occurrence to have an ever-increasing index even though volumes of data have been deleted but knowing what to do to help correct the situation may prove invaluable should the situation arise. In my opinion it’s better to know something you may not need rather than need something you do not know.

My two cents.

About these ads

2 Comments »

  1. Hello,I wanted to use your example to verify if there is a significant performance difference between a normal and a "bloated" B-tree index. My platform is 10.2.0.5 with ASSM and auto-allocated segments.I tried something with this procedure:create or replace procedure biggy_select as n number; m number; l_id biggy.id%type; l_random number; begin select min(id),max(id) into n,m from biggy; for i in 1..200000 loop select trunc(dbms_random.value(n,m)) into l_random from dual; select id into l_id from biggy where id = l_random; end loop; end;Before the delete/insert operations, the index size was 4MB and the execution time of biggy_select was 29.27 seconds (sql_trace = true, tkprof).After the batch delete/inserts, index size grew to 26MB, and the execution time of biggy_select 28.59 seconds…So, I didn't see a significant performance hit… Did you do this kind of performance comparisons?Matthias

    Comment by Matthias Hoys — January 27, 2012 @ 14:01 | Reply

  2. No, I haven't (and probably don't need to since you were so kind to do it) but I expected a minimal performance hit with this particular example as all of the 'dead weight' is composed of leaf blocks attached to a branch that is still populated or was at the time of the inserts. This example used a primary key; possibly a similar example, with a non-unique index and multiple rows having the same key value, would perform differently. I will test that and see what happens.

    Comment by d_d_f — January 27, 2012 @ 23:46 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 724 other followers

%d bloggers like this: