Re: Cost Based Optimizier

> Gene Gurevich <geneg_at_umich.edu> writes:
>
> On 17 Jul 1996 101707.2323_at_compuserve.com wrote:
>
> >
> > We have a 7.1.3.2.1 production system and have just ANALYZED all the
> > tables and indexes.
> > Since we have done this we are suffering with response times from queries
> > that join two and more tables.
>
> You can ran COST-based optimizer in two modes FIRST_ROWS and ALL_ROWS
> (default).

You're still on a pretty early version of Oracle and may find the CBO a bit shaky. We've found in 7.0.15 that it grabs an index which appears optimal using RBO rules even though measured performance with that index is worse. We've also found that analyzing with the ESTIMATE STATISTICS option will sometimes yield a better path than with the COMPUTE STATISTICS option, which seems contrary to common sense -- you'd think that the more complete the statistics, the better the decision, but not necessarily! The index fake-out problem goes away when we try Oracle 7.2.3.

• Paul de Anguera, City of Seattle / HRIS