Re: Cost-based Optimizer

Alain Cheong Chun Lin (alc_at_nyx) writes:
> I need some comments from people who are using the cost-based optimizer. We
> are running Oracle 7.2 on SunOs 4.1.3. Up to now we stick with Rule-based and
> we are doing fine so far. I have decided to try out the cost-based optimizer,
> with lots of care, since I have heard some negative comments on that
> optimizer, especially the earlier versions of Oracle 7.

   The tone of your article indicates to me that you have a good grasp of the issues involved. "Trying out" is by far the best approach. Whether an application is developed with cost or rule based optimization, some performance testing and tuning will always be necessary. Neither optimizer is perfect, they both have been known to do very stupid things. Depending on your application, the change in optimization will improve, degrade, or not effect performance. Depending on the results of your test, you will decide to switch optimizers, retune the application, or do nothing.

   My experience is that the rule based optimizer's greatest strength is it's consistency and predictability. The cost based's strength is it's ability to pick both the best join order, pick the best index(es) to use and react to changes in a highly volatile database.

--
   - Doug Harris
     IS Project Leader, Prices Division,
     Statistics Canada.        ## WHERE ALL_OPINIONS.OWNER = USER ##