Re: From rule based to cost based optimizer

cfischer_at_nando.net (Chris Fischer) wrote:
>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
>

Can you buy Oracle in Toys "R" Us now ??????. Absolutely correct tho' Don't use cost based until 7.2 and even then be careful. Make sure you have stats on all your tables. Stats on one table in a query will cause use of cost based optimiser with unpredictable results Received on Tue Mar 05 1996 - 00:00:00 CET