Re: Cost vs rule based optimizer

In article <35013446.4148671_at_read.news.global.net.uk>, Keith Boulton <boulkenospam_at_globalnet.co.uk> wrote:
>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.

Of course, I have seen hints get improperly used at least as late as 7.3.2. At this point I would say use it, but watch it very carefully at least once for every hint.

>
>
>

-- 
These opinions are my own and not necessarily those of Information Quest
jgarry@eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the @#%*& DBA!