Re: From rule based to cost based optimizer

Kimmo V{tt| <kimmo.vatto_at_ntc.nokia.com> wrote:

>We are planning to move from rule based optimizer to cost based
>optimizer.
>We have an environment with more that 1000000 LOC. The sql-clauses are
>pretty well optimised for the rule based optimizer.
>If you have knowledge of such operation I'd like to hear your opinions
>to the following questions:
>How much of the existing sql-clauses had to be changed?
>How did you spot the clauses that had to be changed?
>What happend to the overall system performance?
>What other things should be taken into account?
>Every tip will be greatly appreciated!

I wouldn't use the cost-based optimizer unless you are using Oracle 7.2 or higher. The biggest challenge for us in moving to cost-based optimization was in keeping the object statistics up-to-date. We ended up having to write a script to use the DBMS_UTILITY.ANALYZE_SCHEMA procedure weekly to keep the performance up to par. This procedure is very lightly documented in Appendix A of the Application Developers Guide.

--Chris Fischer
  Toys "R" Us
  cfischer_at_nando.net Received on Thu Feb 29 1996 - 00:00:00 CET