Re: Proving or Debunking the need for rebuilding

Ben wrote:
> hpuxrac wrote:
> > hasta_l3_at_hotmail.com wrote:
> > > 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
> >
> > What conclusions did you reach based on your approach?
>
>
>
> This is all very good information. I won't be doing nightly rebuilds
> though. This is just a one time ( hopefully ) thing and I wanted to
> make sure that I wouldn't be causing any harm.
>
> Thanks for all the replies thus far, if anyone has any other info
> they'd like to share post it up.

Short of the full-on test, about all you can do is cross your fingers and be prepared to fix any harm. From what I've seen it is pretty rare, and usually due to someone making a mistake like forgetting to cascade the statistics rebuild or getting the statistics at the wrong time (like before new data is loaded). Less often, perhaps, is where data skew requires using histograms, see Jonathan Lewis' optimizer book about that. Jonathan has also posted in various places about how you might cross over a boundary condition causing the optimizer to change its plan. Of course, that tends to be very noticeable to the users of that particular sql. If you run into that, don't panic! Just follow a proper tuning methodology and fix it. If rebuilding an index makes something run worse, that probably means something needed to be fixed anyways.

jg

--
@home.com is bogus.
Be my special friend and lend me your balance sheet.
http://www.signonsandiego.com/uniontrib/20061110/news_1b10enron.html