Re: Clusters and CBO

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 Wed Jul 31 2002 - 13:44:43 CDT