cost based optimizer vs rule based optimizer

cost based optimizer vs rule based optimizer

Post by Kel Bah » Fri, 05 Jun 1998 04:00:00



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

 
 
 

cost based optimizer vs rule based optimizer

Post by Robert Prend » Fri, 05 Jun 1998 04:00:00


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


>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



 
 
 

cost based optimizer vs rule based optimizer

Post by Brian Wheele » Fri, 05 Jun 1998 04:00:00


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


> 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


 
 
 

cost based optimizer vs rule based optimizer

Post by Merle Martel » Sat, 06 Jun 1998 04:00:00



> Default optimizer is COST for Oracle 8, I think...

In Oracle8, default OPTIMIZER_GOAL parm value is CHOOSE, which will use
RULE optimizer when there are no statistics for the table, and will use
COST when there are statistics for the table.  Statistics are captured
by running the ANALYZE command.
 
 
 

cost based optimizer vs rule based optimizer

Post by Joel Gar » Sat, 06 Jun 1998 04:00:00



>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


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


--
These opinions are my own and not necessarily those of Information Quest
or Pebble In The Sky                     http://www.informationquest.com


 
 
 

cost based optimizer vs rule based optimizer

Post by David Sis » Tue, 09 Jun 1998 04:00:00


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


>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


 
 
 

cost based optimizer vs rule based optimizer

Post by Simon Griffith » Fri, 26 Jun 1998 04:00:00



> For large tables, you can save time by analyzing just a percentage of the table:

> example:      analyze table XXXXX estimate statistics sample 10 percent;

> This should provide good statistics for CBO without taking the time to analyze the
> whole table.

absolutely !

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.