Re: Cost-Based or Rule-Based?

dave_dizon_at_hotmail.com wrote:

> So I am curious,how many installations out there are using or planning to use
> the Cost-Based Optimizer (CBO) instead of the Rule-Based Optimizer (RBO)?
> And for those who have the experience on both of RBO and CBO which is the
> better of the two?

CBO without any doubts is better than RBO. At our site we have several Oracle DBs

and PeopleSoft applications that were optimized for RBO. Switching optimization mode for slow SQL expressions increased speed in 50-2000 times. I had one expression that in RBO mode ran for 3h 5 min, in CBO - 5 sec. In some case you will have the same execution plans (that means the same speed). Two times I saw a "performance holes" - CBO choose wrong indexes, and performance in these cases was awful - 4-6 hours instead of 10 min. So I had to fix it manually, with hints.

Oracle CBO can do a good job, optimizing expressions. You only need to provide information to it - regularly analyze your object (tables, indexes, clusters), and
create proper indexes for faster access to data. Also you can optimize with histograms,
this can significantly improve your expression in some cases.

Peter Received on Sat Jan 23 1999 - 16:17:29 CST