>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
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.
>>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.
These opinions are my own and not necessarily those of Information Quest
or Pebble In The Sky http://www.informationquest.com