Re: RBO vs CBO

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<05Qh9.35033$g9.98760_at_newsfeeds.bigpond.com>...
> "Jennifer" <Jennifer_at_DAG[DELETEME].ca> wrote in message
> news:gPHh9.12512$V21.290128_at_news...
> >
> > Is it true that, even if the OPTIMIZER_MODE is set to RULE, that once
> > ANALYZE is performed on a table, the Cost Based Optimizer is invoked
> > instead?
> >
>
> Hi Jennifer,
>
> No, it's not true. If the OPTIMIZER_MODE is set to rule then statistics have
> no effect.
>
> However if the OPTIMIZER_MODE was set to CHOOSE, then yes it would have an
> effect (as it uses RBO if no referenced object has statistics and uses CBO
> if any referenced object has statistics).
>
> Note that hints also uses the CBO regardless if OPTIMIZER_MODE is set to
> rule. The only exception to this is the RULE hint.
>
> > If this is not the case, how can I switch to the cost-based optimizer
> while
> > keeping the OPTIMIZER_MODE = RULE in the "init.ora" file?
>
> You can switch to the CBO in one of two ways:
>
> 1) ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS (or FIRST_ROWS or CHOOSE) Note:
> 9i has a number of new first_row options.
>
> 2) Use any appropriate hint (SELECT /*+ hint */ ...)
>
> Cheers
>
> Richard
> >
> > --
> > _________________________________
> > Jennifer Champagne
> > Decision Academic Graphics Inc.
> > Tel: (613) 233-2365 x226
> > Email: Jennifer.Champagne_at_DagSoft.com
> >

To add to what Richard said that starting with 7.3 if any hint other than RULE is in the SQL statement the CBO is used. This is true even if there are no statistics on the underlying tables. The CBO will just make some quick assumptions so if you are going to have your DB set to RULE in the init.ora but some of your SQL is hinted you will want to generate statistics on the objects.

Note that before using dbms_stats for any version prior to 8.1.7 you may want to check metalink and look at some of the problem reports. There were several reports about it's falure to handle parameters correctly and in one case would generate statistics on the base tables as a side effect. But as long as you are aware of the couple of problem areas it works.

• Mark D Powell --