Did you get any reply (either from Oracle or someone else) about how to fix incorrect
result bug due to cost based optimizer ? I am finding some queries which return wrong
results when table is analyzed. Results are correct when analyze stats are removed.
Rdbms is 733 on Sun.
> Subject: Re: cost based optimizer vs rule based optimizer
> From: joelga_at_pebble.org (Joel Garry)
> Date: 1998/06/05
> Message-ID: <slrn6ngsc0.kjo.joelga_at_pebble.org>
> Newsgroups: comp.databases.oracle.tools;,comp.databases.oracle.server
> [More Headers]
> [Subscribe to comp.databases.oracle.server]
>
> On Thu, 04 Jun 1998 23:49:18 GMT, Robert Prendin <rprendin_at_magi.com> wrote:
> >From personal experience, cost based blows away the rules based
> >optimizer. Cost based is a much more intelligent optimizer. Remember
> >to analyze your tables and indexes so that the optimizer actually has
> >statistics to work with, otherwise it is pointless....
>
> Worse than pointless, it can actually give incorrect results, such as
> only getting 19,000 rows when there are actually 43,000 in one case I just
> experienced.
>
> Changing results by changing hints can be quite mortifying, especially when
> the _rule based_ optimizer gives the wrong results. 7.3.4 on Solaris 2.6.
> (This particular case involved putting an index on a low-cardinality
> column. The engineer complained to me that his query never finished, pinning
> perfmon after spitting out 19,00 rows. After some investigation, I discovered
> it would eventually finish, but not get many more than the 19,000 rows. It
> is as though the oracle kernel code has a time-out in it that assumes if
> nothing has come back in a certain time, it must have gotten all possible
> rows that are coming back. This is _not_ Parallel Server. I tried
> CBO-forcing hints, and discovered the same incorrect results with first_rows,
> different incorrect results with all_rows, then correct CBO results after
> analysing everything. I left it at that, deciding that Oracle would
> probably just give me grief about the ridiculous index if I tried to tell
> them there was a bug with RBO. Why fix something that no one would want
> to do? )
>
> My general feeling (and Oracles explicit direction) is that Oracle is moving
> to the CBO, so one should write all new code with the expectation that it
> works and Oracle will be responsible for making sure that it does.
>
> Personally, I've seen so many CBO bugs in past years, I'm a bit shy of it, but
> that's more my problem than Oracle's. It does require some thought to be
> sure that it is not being fooled by a particular circumstance, and with the
> separation of duties of programmers and DBA's there is a definite risk of
> miscommunication - This could be a more subtle problem than under an RBO
> situation, where you can just blame the programmer for not knowing the rules.
>
> It winds up being a lot of work for the DBA coming into an ongoing production
> system with all sorts of unknowns.
>
> >
> >
> >Cheers,
> >
> >Robert Prendin
> >
> >
> >Kel Bahi <kbahi_at_creo.com> wrote:
> >>Hi
> >>Does anyone have any opinions on which optimizer to use?
> >>Are they comparable? Is one always better than the other?
> >>Does it depend on the database ( volume, rate of change)
> >>
> >>Any info appreciated.
> >>BTW we are using oracle 8.0.3 under NT.
> >>
> >>Thanks
> >>Kel Bahi
> >>Creo Products
> >>kbahi_at_creo.com
> >
>
> --
> These opinions are my own and not necessarily those of Information Quest
> or Pebble In The Sky http://www.informationquest.com
> http://ourworld.compuserve.com/homepages/joel_garry jgarry@nospameiq.com
> "See your DBA?" I AM the @#%*& DBA! Remove nospam to reply. Sorry.
>
> -----------------------------------------------------------------
> [Do you have a unique product?]
> Click here to create your free discussion forum!
> [Email a Friend]
> << >> /\ * Help * Post New
> Previous Next Current * Author Profile * Post Reply * Bookmark
> Article Article Results Email this * View Thread * Email Reply* Text Only
> message!
> -----------------------------------------------------------------
> Directories | Find People Now! | Classifieds | Yellow Pages
>
> Register Your Domain Name | Auctions | Free Coupons!
>
> New Users · About Deja News · Ad Info · Our Advertisers
> Free Web Email · Link to Deja News · Create Your Own Forum
>
> Home · Search · Browse · Post · My Deja News · Help ·
> How are we doing?
>
> -----------------------------------------
> Copyright © 1995-98 Deja News, Inc. All rights reserved.
> Conditions of use.
Received on Tue Jul 14 1998 - 13:07:38 CDT
