Re: Proving or Debunking the need for rebuilding

hasta_l3_at_hotmail.com wrote:
> >
> > What conclusions did you reach based on your approach?
>
> On my system - and I stress : on my system
>
> - It took 40 minutes to rebuild the (150) hot indexes.
> Further rebuilds took the same time +/- 15%
>
> - Time to perform most common querying user
> actions was slightly improved. The measured
> improvement was probably within the error margin.
> An occasional 30% improvement was seen for medium
> size selects (4000 rows)

You said "The measured improvement was probably within the error margin.".

To me at least that sounds like "no net gain" but the phrasing "most common querying user actions" makes me nervous. Do you have specific test cases or are you just generalizing? Without specific test cases this kind of exercise is ... not exactly authoritative.

What selects saw a 30% improvement? Was that in response time? Any OLTP application that is retrieving 4000 rows repeatedly is going to vary in response time anyway based on transient conditions, at least in my experience. Again the phrase "An occasional" makes me wonder if this is based on test cases or what exactly.

>
> - Time to perform a long night job dropped from five
> to two hours.

So what exact SQL related component of the long night job was impacted?

It might be that only 1 of the indexes changed affected your "long night job" so perhaps your rebuilding of the 150 indexes could be targeted against only 1 index conceivably?

>
> Unfortunatly, I didnt time update actions, not did I
> check the rate of redo log switches, nor did I compare
> the size of indexes.

These are all important components. I think both Tom Kyte and Jonathan Lewis have noted that over time indexes want to / tend to shape themselves against relevant activity.
>
> 9.2.0.5
>
> --- Raoul

Thanks for being brave enough to post this! Received on Tue Nov 14 2006 - 07:49:34 CST