Re: rebuild index?

You do use pct_used as a guideline.
As with all 'average' statistics if the statistics looks bad it is worth investigating. So an index with 50% of the total space empty is worth looking at.

BUT there may be sound business reasons why this does not matter, so the only thing that tells you that the index NEEDS to be rebuilt is knowledge of the application.

What if:
a) You run your report at 3 am each day, and

    at 4:30 am some batch process runs which     expects to fill all the holes so that the day time     processing is running close to 100% usage     on the index ?

    If you rebuilt the index at 3:30, then the batch would     probably split every block in sight and leave the day     time processing running with the index at 50%     efficiency.

b) What if the first 99 blocks have one index entry

     each and the last block has 99 entries (numbers     taken for ease of demonstrating the point) then     the index is running at 50% used. But if the     application is in some way guaranteed to used     only the most frequent entries, then the 99     'bad' blocks are affecting performance. You     could rebuild the index but it isn't urgent.

One other guideline on rebuilding:
Run a daily (or weekly) report of the high water mark of every object in the schema. If an index is growing rapidly and has a low efficiency (pct_used) then it is fairly likely to be a candidate for a regular
rebuild. If an index suddenly jumps
in a big way, its hwm and drops it pct-used, it is fairly likely to be a candidate for a one-off rebuild.

Hope this helps.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

David Spaisman wrote in message <376715C1.73637DA1_at_intercall.net>...
>So aside from the browning factor and reaching close to max extents,
>when do I rebuild an index? If pct_used is sometimes sparse at one end
>and at the other end of the tree and if blks_gets_per _access is only an
>estimate, what else do I use to tell that the index needs to be rebuilt?
>When do you recreate your indices?
Received on Wed Jun 16 1999 - 02:03:12 CDT