Re: Proving or Debunking the need for rebuilding

Ben wrote :

> 9.2.0.5 EE AIX5L
>
> Ok so I do believe everyone when they say to quit rebuilding indexes,
> except for certain special cases ( like large purges on tables that
> will never grow again unless the moon is full and the wind blows
> eastward). I do understand that rebuilds can be harmful, BUT how do I
> prove one way or the other?
>
> I want to create matrices on the before and after of an index rebuild.
> How? I don't want to blindly say, "Yes this index needs to be rebuilt"
> I want to show the performance gain or loss from a rebuild. Surely
> there is a way to do this. Do you simply anayze ... validate and
> capture the statistics on the indexes?
>
> Thanks for any ideas and help.

I checked exactly this last month. In retrospect, I would use this methodology :

1. Setup a load test environment duplicating system activity
2. Let the system run for 15 days, without index rebuild.
3. Time a small representative set of transactions, including
   • Most common querying user action(s)
   • Most common updating user action(s)
   • Nightly batch jobs
4. Rebuild all indexes
5. Time again
6. Run one week with nightly rebuilds
7. Time again
8. Believe your data