Re: How many people here rebuid index regularly?

On Oct 6, 6:05 pm, hjr.pyth..._at_gmail.com wrote:
> On Oct 6, 5:13 pm, csn..._at_gmail.com wrote:
>
> > > I realise this would take time, but have you considered a treedump of
> > > the indexes before and after the rebuild:
>
> > > alter session set evnts = 'immediate trace name treedump level <index
> > > object id>';
>
> > > You should see considerable differences in the dump files for a given
> > > index if the rebuild of it improves performance. And those file pairs
> > > with differences would indicate the indexes upon which you should
> > > concentrate your rebuild efforts.
>
> > Comparing index sizes before and after rebuild would probably reveal
> > all there is to know.
>
> No, it wouldn't be all there is to know.
>
> If an index settles down at about 75% space utilisation in a steady-
> state scenario, it stands to reason that a rebuild will show it small
> after the rebuild that before. But if you were to conclude from that
> that the rebuild was obviously of benefit, and that the reduction in
> size was "all you needed to know", you'd be missing a trick or three.
>
> Does the now-compacted index now suffer from higher contention rates
> than it used to? It could well do so. Do the next X-number of inserts
> to the table now take place slower than they did before because they
> now have to cause the index to re-acquire the empty space it
> previously already had available to it? It could well be the case.
>
> Size is NOT everything, in short.

Ah, the perils of making sweeping statements! Let me try again. I was replying to the treedump post and comparing looking at index sizes vs doing treedumps.

In a bulk rebuild, most indexes would flip from 75% to 90% utilisation (or whatever you set the new pctfree to be), and the sizes would vary accordingly. No surprises there.

However, some indexes would shrink in size by a far greater proportion. Those ones you examine a little closer. That and the fact that I (and I suspect the majority of people reading this, though I could be wrong) am not able to draw any meaningful conclusions as to whether a rebuild is warranted from looking at treedumps. The last time I looked, it just looked like a tree to me, something which I already knew.

Size, on the other hand, tells me that either a bulk delete has occurred, or the index is possibly suffering from monotonic inserts/ deletes. The first one needs no future action if the delete was a known one-off. The second one is a candidate for future rebuilds. And that is all I need to know.

If I inherit a database with a dubious history, I would schedule a mass rebuild for these reasons, plus the fact that you get every extent size possible under the blue sky. Especially, when you are required to add/resize datafiles at a rate out of kilter with expected growth rates. The trick however is deciding on a good pctfree for the individual larger indexes, especially when you have a quite a few to contend with. But that is another story. Received on Sat Oct 06 2007 - 06:09:59 CDT