Very Computer

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

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....

Cheers,

Robert Prendin

Default optimizer is COST for Oracle 8, I think...
Whatever you do, don't use COST for Oracle Financials!  That
package is designed to use RULE.

Thats my 2 cents worth

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.

Hi Kel:

Cost seems to work best for us.  I think they both have their bugs, but CBO
supports more advanced features such as table and index partitioning, etc.

Good luck,
Dave

but also...
We have tables of 5Gb + and so even 10% is too much.
We tend to use "sample 5000 rows" or such like and
get reasonably good stats.

Simon.