Re: Clusters and CBO

Svend

Thank you, that is certainly part of the problem. Although having tried again with the cluster stats computed things have definitely speeded up, although it still doesn't beat the CBO on one of the main batch processing tasks. I have never noticed that hole in dbms_stats, presumably it is documented in the guides somewhere, I'll have to have another look.

Jonathan

"Svend Jensen" <svend.jensen_at_it.dk> wrote in message news:3D48301B.7030607_at_it.dk...
> Jonathan Bliss wrote:
>
> > Hi
> >
> > I am working with an application originally written in Oracle 6. A
major
> > set of reference tables are built as a cluster. These reference tables
are
> > highly normalized and self referencing. i.e. an item in the main table
can
> > be related to other items in the same table that can be in turn related
to
> > others. The application appears to run more slowly with the CBO than
with
> > the rule based optimizer thus the live environment uses the RBO. The
> > culprit appears to be when multiple aliases of the reference tables are
> > used. The entire application is written in PL\SQL. I didn't write it.
> >
> > 1. Should the CBO *always* outperform the RBO.
> > 2. Are clusters one instance this may not be the case.
> > 3. Is Oracle really going to take away the RBO
> >
> > Tests suggest that rebuilding the tables without the cluster, computing
> > stats and using CBO may not degrade performance but it takes courage to
bite
> > the bullet with the live system.
> >
> > Any thoughts appreciated.
> >
> > Jonathan
> >
> >
> >
>
> Have you estimated/computed statistics using dbms_stats package.
> There is a small aber dabei - dbms_stats doesn't compute for clusters.
> Hence you have to use 'analyze table/cluster compute statistics
> for all indexed columns size 1' or something similar.
> First law of success with CBO is decent statistics.
>
> /Svend Jensen
>
Received on Thu Aug 01 2002 - 09:35:41 CDT