Re: Cost vs rule based optimizer

On Wed, 04 Mar 1998 08:12:31 -0800, "Kenneth J. Ingram" <kingram_at_navis.com> wrote:

>Their DBA insists CBO is a failure and cites second-hand information
>about
>a conference where, when asking a room full of DBA's who used CBO
>instead of RBO
>not one person raised their hand.
>
>My suggestion to use CBO seemed to improve the query in question, but
>other
>problems arose. I think they failed to run the ANALYZE often enough or
>maybe not
>at all.
>
>I'd like to know who refuses to use CBO and why. If it's not too much
>trouble
>forward me any answers you get.
>
>Thanks
>
>Ken Ingram
>Navis Corporation
>
>
>

My experience with the cost based optimiser was that it doesn't make a difference to most queries. It makes a small improvement in performance for some queries and a very large worsening of performance in others. It tends to do full table scans too often. There is a nasty feature (in version 7) whereby a range scan will never use an index with a bind variable i.e. where column > :value.

Like most things provided by oracle ( and microsoft and ...) version 1 is crap. Version 1.x has bugs removed. Version 2.0 would be okay if it worked properly and version 2.x finally gets to the point of being useful.

The big problem with the cost based optimiser is that it is not predictable. Often you need to know that a particular process will run in a given time. The main reason for using the cost based optimiser, it seems to me, is that you can use hints to control the access path used. Received on Sat Mar 07 1998 - 00:00:00 CST