Mohamed Houri’s Oracle Notes

July 27, 2013

Index Coalesce : sys_op_lbid

Filed under: Oracle — hourim @ 8:44 am

A recent question on the oracle French forum about rebuilding indexes remembered me to write a small note to show the existence of a more interesting option to use when maintaining an index but unfortunately not widely used. It is the COALESCE index command. Let me put a simple model and explain (a) how to identify indexes that might benefit from the COALESCE and (b) how to show the effect of the COALESCE command on this kind of indexes.

create table t1 as
 select
 trunc(sysdate) + rownum d1,
 rownum n1
 from dual
 connect by level <= 1e6
 ;
create index ind_d1 on t1(d1);

exec dbms_stats.gather_table_stats (user, 't1');

In order to simulate a real life example of an index evolution I will try to update this index several times for the same range of data until it reaches a final stage

begin
 2 for j in 1..100
 3 loop
 4 update t1
 5 set d1 = trunc(d1) + j
 6 where t1.n1 between 15000 and 20000;
 7 end loop;
 8 commit;
 9 end;
 10 /

PL/SQL procedure successfully completed.

begin
 2 for j in 1..100
 3 loop
 4 update t1
 5 set d1 = trunc(d1) - j
 6 where t1.n1 between 15000 and 20000;
 7 end loop;
 8 commit;
 9 end;
 10 /

PL/SQL procedure successfully completed.

begin
 2 for j in 1..100
 3 loop
 4 update t1
 5 set d1 = trunc(d1) - j+1
 6 where t1.n1 between 15000 and 20000;
 7 end loop;
 8 commit;
 9 end;
 10 /

In order to know if my index can benefit from the coalesce command, I will use the sys_op_lbid internal oracle function which gives the number of Keys(leaf block id) per blocks.

select object_name,object_id
from user_objects
where object_name = 'IND_D1';

OBJECT_NAME OBJECT_ID
-------------------- ----------
IND_D1 509423

select
 2 keys_per_leaf, count(*) blocks
 3 from (
 4 select sys_op_lbid (509423, 'L', t1.rowid) block_id,
 5 count (*) keys_per_leaf
 6 from t1
 7 where d1 is not null
 8 group by sys_op_lbid (509423, 'L', t1.rowid)
 9 )
 10 group by keys_per_leaf
 11 order by keys_per_leaf;

KEYS_PER_LEAF BLOCKS
------------- ----------
 2 102
 3 451
 4 352
 5 133
 6 153
 7 72
 8 98
 9 22
 10 63
…..
 134 1
 140 1
 156 1
 196 1
 206 1
 215 1
 292 1
 377 2611
62 rows selected.

This is a smashed index as you might have pointed out that in order to access to 2 leaf block keys we need to visit 102 blocks worth of data and to get 3 other leaf block keys we need to access 451 blocks and so on. This index needs really to be COALESCED. Before coalescing it let me show you how space is spread within this index:

 begin
 p_check_free_space (user, 'IND_D1', 'INDEX');
 end;
 /
Number of Blocks with 0-25% free space = 0 -------> Total Bytes = 0
Number of Blocks with 25-50% free space = 1459 -------> Total Bytes = 11.3984375
Number of Blocks with 50-75% free space = 0 -------> Total Bytes = 0
Number of Blocks with 75-100% free space = 0 -------> Total Bytes = 0
Number of Full Blocks with no free space = 4407 -------> Total Bytes = 34.4296875

Total Blocks : 5866
Total Size MB: 46.928

Now it’s time to coalesce this index and get its new leaf block key distribution

alter index ind_d1 coalesce;

Index altered.

select
 2 keys_per_leaf, count(*) blocks
 3 from (
 4 select sys_op_lbid (509423, 'L', t1.rowid) block_id,
 5 count (*) keys_per_leaf
 6 from t1
 7 where d1 is not null
 8 group by sys_op_lbid (509423, 'L', t1.rowid)
 9 )
 10 group by keys_per_leaf
 11 order by keys_per_leaf;

KEYS_PER_LEAF BLOCKS
------------- ----------
 14 1
 29 1
 35 1
 167 1
 196 1
 210 1
 309 1
 367 1
 377 2649

9 rows selected.

Spot how the index become now (after it has been coalesced) more attractive as far as to get 14 (29, 35 or 367) keys of leaf blocks id we need to visit only one block.

Finally let me show you the new index space configuration the coalesce command has produced

begin
 p_check_free_space (user, 'IND_D1', 'INDEX');
end;
/
Number of Blocks with 0-25% free space = 0 -------> Total Bytes = 0
Number of Blocks with 25-50% free space = 3197 -------> Total Bytes = 24.9765625
Number of Blocks with 50-75% free space = 0 -------> Total Bytes = 0
Number of Blocks with 75-100% free space = 0 -------> Total Bytes = 0
Number of Full Blocks with no free space = 2669 -------> Total Bytes = 20.85

Total Blocks :5866
Total Size MB:46.928

The coalesce command did not changed the size of the index nor the number of its total blocks. However, it did make a nice data distribution since it freed up 1736 new blocks ( 4407-26669) and made 1768 (3197-1429) new blocks offering 25-50% of free space.

Before closing this blog article I would like to emphasize that if you arrive to the conclusion that your index needs to be coalesced then think about a periodic (each week for example) coalesce of this index. Because the conditions which smashed your index (delete of old data from left hand side of the index and insert new data into the right hand side of the index) is still present and will sooner or later smash your index so that a new coalesce will be necessary.

7 Comments »

  1. I wonder how often my DBAs rebuild our indexes. *sigh*

    Comment by 0livier (@0livier) — July 28, 2013 @ 7:09 pm | Reply

  2. It would be safe if they never rebuild your indexes. Rebuilding indexes should not be put under the hands of inexperienced DBAs as far as there is more chance that the rebuild will end up (sooner or later) to be of no effect in better cases and to dramatically alter the performance in worst cases.

    That’s said, there are cases where an index might benefit from a rebuild(http://jonathanlewis.wordpress.com/category/oracle/indexing/index-rebuilds/). However, I doubt that today DBAs will investigate the benefit of rebuilding their indexes before jumping to the rebuild itself. This is without mentioning that many DBAs ignore the difference between rebuilding an index offline and online and the strategy used internally by Oracle in this two particular cases of rebuild.

    Cheers

    Comment by Houri — July 29, 2013 @ 11:11 am | Reply

  3. Dear Mohamed,
    Thank you very much for this very interesting post.
    I thought the coalesce functionality was automatic in the tablespaces from the10G onward.
    Obviously, we need to program it from time to time, once indexes have been selected as potentially disorganized.
    I wonder where is the p_check_free_space function available on your website because I would like to use it.
    Thanks in advance.

    Jena-michel A., Nemours, FRANCE

    Comment by alzingre — March 20, 2014 @ 11:22 am | Reply

  4. Jean-Michel,

    Merci pour votre message

    Here below is the p_check_free_space definition

    create or replace
    PROCEDURE p_check_free_space (
       p_owner          IN   VARCHAR2 DEFAULT USER,
       p_segment_name   IN   VARCHAR2,
       p_segment_type   IN   VARCHAR2
    )
    IS
       l_fs1_bytes            NUMBER;
       l_fs2_bytes            NUMBER;
       l_fs3_bytes            NUMBER;
       l_fs4_bytes            NUMBER;
       l_fs1_blocks           NUMBER;
       l_fs2_blocks           NUMBER;
       l_fs3_blocks           NUMBER;
       l_fs4_blocks           NUMBER;
       l_full_bytes           NUMBER;
       l_full_blocks          NUMBER;
       l_unformatted_bytes    NUMBER;
       l_unformatted_blocks   NUMBER;
    BEGIN
       DBMS_SPACE.space_usage (segment_owner           => p_owner,
                               segment_name            => p_segment_name,
                               segment_type            => p_segment_type,
                               fs1_bytes               => l_fs1_bytes,
                               fs1_blocks              => l_fs1_blocks,
                               fs2_bytes               => l_fs2_bytes,
                               fs2_blocks              => l_fs2_blocks,
                               fs3_bytes               => l_fs3_bytes,
                               fs3_blocks              => l_fs3_blocks,
                               fs4_bytes               => l_fs4_bytes,
                               fs4_blocks              => l_fs4_blocks,
                               full_bytes              => l_full_bytes,
                               full_blocks             => l_full_blocks,
                               unformatted_blocks      => l_unformatted_blocks,
                               unformatted_bytes       => l_unformatted_bytes
                              );
       DBMS_OUTPUT.put_line (   ' Number of Blocks with 0-25% free space  = '
                             || l_fs1_blocks
                             || '                                      -------> Total Bytes = '
                             || l_fs1_bytes/1024/1024
                            );
       DBMS_OUTPUT.put_line (   ' Number of Blocks with 25-50% free space  = '
                             || l_fs2_blocks
                             || '                                      -------> Total Bytes = '
                             || l_fs2_bytes/1024/1024
                            );
       DBMS_OUTPUT.put_line (   ' Number of Blocks with 50-75% free space  = '
                             || l_fs3_blocks
                             || '                                       -------> Total Bytes = '
                             || l_fs3_bytes/1024/1024
                            );
       DBMS_OUTPUT.put_line (   ' Number of Blocks with 75-100% free space = '
                             || l_fs4_blocks
                             || '                                        -------> Total Bytes = '
                             || l_fs4_bytes/1024/1024
                            );
       DBMS_OUTPUT.put_line (   ' Number of Full Blocks with no free space = '
                             || l_full_blocks
                             || '                                        -------> Total Bytes = '
                             || l_full_bytes/1024/1024
                            );
       dbms_output.put_line('Total Bolcks ______________________________');
       dbms_output.put_line(l_fs1_blocks + l_fs2_blocks + l_fs3_blocks + l_fs4_blocks + l_full_blocks );   
       dbms_output.put_line('Total Size MB______________________________');
       dbms_output.put_line((l_fs1_bytes + l_fs2_bytes + l_fs3_bytes + l_fs4_bytes + l_full_bytes)/1024/1000 );                  
       
    END p_check_free_space;
    

    Bien Cordialement

    Comment by hourim — March 20, 2014 @ 11:30 am | Reply

  5. […] sys_op_lb_id function when applied on this index gives the following average leaf block per index key […]

    Pingback by Index Efficiency | Mohamed Houri’s Oracle Notes — May 12, 2015 @ 7:03 am | Reply

  6. Hi Mohamed

    As I mentioned in your Index Efficiency blog post (https://hourim.wordpress.com/2015/05/12/index-efficiency/#comment-2309), you’re reading the data incorrectly. The histogram is simply stating how many index entries you have in your leaf blocks, not having many leaf blocks need to be visited per index entry.

    So your comment for example “in order to access to 2 leaf block keys we need to visit 102 blocks” is incorrect. It means that 102 leaf blocks have just 2 index entries. These index entries could well be unique and you only have to visit the one leaf block for each of these index entries.

    Note there’s nothing here to suggest the index was really that “smashed” and the coalesce has simply increased the number of blocks with the maximum number of index entries (those with 377 entries) slightly from 2611 to 2649 and reduced some leaf blocks with fewer entries.

    Hope this makes sense 🙂

    Comment by Richard Foote — May 13, 2015 @ 1:59 am | Reply

  7. Hi Richard,

    Thanks a lot for your comments here and in the other blog post. I highly appreciate

    Best regards
    Mohamed Houri

    Comment by hourim — May 13, 2015 @ 12:17 pm | 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

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: