Re: risks of using cost based optimizer

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. Received on Thu Nov 07 2002 - 16:53:39 CET