Re: Migrating to cost based optimizer

hasta_l3_at_hotmail.com wrote:
> Dear group,
>
> Our applications are supporting Oracle 8.1.7 and Oracle 9, for many
> customers with varying level of activity.
> We use the rule based optimizer to ensure the stability of the sql
> statement's execution plans.
>
> We are thinking of moving to the cost based optimizer, at least in
> Oracle 10.
>
> Would you have a strategy to recommand ?
>
> What is the first Oracle release with a cost based optimizer working
> reasonably well ?
>
> How do you check the performances of your applications across oracle
> releases and customers with that optimizer ?
>
> Thanks.

I agree with the other posted answers, and would add a couple of thoughts:

You are likely to have some code that has been tweaked for RBO. Oddly enough, this won't make a lot of difference, because many of the RBO rules like order of table names or predicates no longer matter. My experience has been, you will probably find a couple of things where another index will make a lot of difference, because the RBO will have not had the choices CBO does. This should come out in your testing, especially with discrete canonical data sets. You should look through your code for things like strange extra WHERE conditions. You don't want to through curveballs to the CBO if you can avoid it.

There are also configuration changes needed. This is pretty basic stuff, but you need someone to know what changes are needed and what to watch for. This will be for every customer. Hopefully your canonical data sets will help you come up with some rules of thumb. Don't let implementors and support staff fall into the trap of thinking rules of thumb are canonical. Avoiding that trap is much harder than it sounds, since everyone wants concise and accurate cookbook installation instructions, not iterative tuning procedures.

You are starting with no CBO experience, just think about how your customers will be another level removed from that. You may be lucky (or unlucky :-) to have some customers _with_ Oracle knowledge. This means you must have staff qualified to deal with this range of customers, immediately when they call for help. These CBO-specific problems are often quite simple for the initiated, but really upset users who just want it to "work." CBO also has developed its own myths and religions you have to watch out for. That said, I think lost, misleading or out of date statistics is still the most common problem. You will need to come up with some coherent answers for people who ask, say, whether to use plan stability or to rebuild statistics every night, what should I set optimizer_index_caching and optimizer_index_cost_adj to.

See http://www.evdbt.com/SearchIntelligenceCBO.doc

Some vendors just say "it's up to the customer, we're not their dba." That is not good customer relations. But working with customers who appreciate free tuning work can make some good lemonade.

You should also be aware of when CBO is used, some of the things that can set it off when you think it is using RBO can be surprising at first. Read those manuals!

jg

--
@home.com is bogus.  "Voilą! In view, a humble vaudevillian veteran,
cast vicariously as both victim and villain by the vicissitudes of
Fate. This visage, no mere veneer of vanity, is a vestige of the vox
populi, now vacant, vanished. However, this valorous visitation of a
by-gone vexation, stands vivified and has vowed to vanquish these venal
and virulent vermin van-guarding vice and vouchsafing the violently
vicious and voracious violation of volition." - V