Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: cost based optimizer vs rule based optimizer

Re: cost based optimizer vs rule based optimizer

From: Joel Garry <joelga_at_pebble.org>
Date: 1998/06/05
Message-ID: <slrn6ngsc0.kjo.joelga@pebble.org>#1/1

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.
Received on Fri Jun 05 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US