Re: risks of using cost based optimizer

bpathakis_at_yahoo.com (Brent) wrote in message news:<1736c3ae.0211070753.179812c5_at_posting.google.com>...
> vbauzac_at_capgemini.fr (Vincent BAUZAC) wrote in message news:<85372317.0211060830.13d446ef_at_posting.google.com>...
> > Hi,
> >
> > We have a production database (Oracle 8i) still using the 'RULE'
> > optimizer mode.
> > Since we have some performance troubles, we would like to switch it
> > into the 'CHOOSE' optimizer mode and compute the statistics, every
> > weeks for example.
> >
> > The Oracle documentation advises to use the statistics, but we have
> > heard there is some risks in doing this; the performance of some
> > request could be badly affected.
> >
> > Is it true ?? What kind of request could be badly affected ??
> >
> > Thanks for your help,
> >
> > Vincent
>
> If it was written and optimized for the rule based optimizer, then
> switching to CBO could cause serious performance problems.
>
> What I would do (in a test environment or during downtime):
>
> * Make sure the optimizer mode is choose.
> * Gather statistics on all tables / indexes.
>
>
> I would also modify the settings (using alter session to test, and
> change them if in your init.ora if they work):
>
> * optimizer_index_cost_adj - usually needs to between 10-30,
> defaults to 100
> * optimizer_index_caching - I would start with 50, and experiment.
>
>
> And test your apps / queries to see how they perform. If they don't do
> well
> with the CBO, delete the statistics and it will go back to using rbo.

Be forewarned that with CHOOSE, if any of your tables get analysed, that can make for some very bizarre performance "issues." This can happen if, for example, you export a table with stats, and then import it later. Or even if you just miss the stats when you delete them. Or...

jg

--
_at_home is bogus.
I've seen CHOOSE bite people in the butt months or perhaps years
later...