Cost-Based or Rule-Based??.. That's the question..
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Cost-Based or Rule-Based??.. That's the question..

  1. #1
    Join Date
    Sep 2001
    Posts
    37

    Cost-Based or Rule-Based??.. That's the question..

    This kinda problem will look familiar to you guys..

    I was looking for chained rows on the most used tables (Not all) in our DB, so i analyzed them to get some info.

    The result of this process (besides the info off course) was a Low DB Performance in querys that were working fine before this process (and use at least one of the tables that were analyzed).
    I had to delete the statistics to recover the previous performance.

    This behavior was expected because the Optimizer Mode Parameter was set to CHOOSE in our DB, and the query involved tables with stats and tables without them. This resulted in sub-optimal performance because Oracle enabled Cost-Based Optimization and guessed stats for tables that actually donít have them. However Queryís performance was fine in Rule-Based Optimization.

    So... I read somewhere that Cost-Based Optimization is better than Rule Based Optimization, and if this is true i would like to take benefit on it.

    Is this True??.. (i donít know really)
    Or depends on certain conditions (If it does.. Wich ones??)

    The fact is that i donít want to compute statistics for all my tables to figure this out... because Low Performance in Production DB right now is not an option. Maybe the appplication could not be prepared for this kind of optimization and will have Low Performance anyway after all this work..

    Maybe you can help with some advices...

    Any help would be appreciated..

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Have you looked at setting up a test system to determine if stats will help. You could setup up a system from an import. If you want to gen stats without effecting the optimizer change the optimizer_mode to rule.

    Whether or not cbo or rule is faster depends on a lot of factors including: version of Oracle including which patches are installed, how you generate stats, how the tables are layed out in your database and what kinds of hints if any you use. Setting up a test system is the only way to know for certain without effecting your prod db's performance.

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    There's no question. Get used to CBO as it will be the ONLY optimizer available in the future.

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Oracle Corporation strongly advises the use of cost-based optimization. The rule-based optimization is available for backward compatibility with legacy applications and will be deprecated in a future release.

    http://download-west.oracle.com/docs...mops.htm#40574

  5. #5
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Oracle 10i will not have the Rule-Based Optimizer...
    OCP 8i, 9i DBA
    Brisbane Australia

  6. #6
    Join Date
    Sep 2001
    Posts
    37
    Looks like i'll have to read a little more about CBO..

    Anyone can suggest a good source of information for Query's Optimization, Hints & Tips or stuff like that ??

    Thanks in advance

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    tahiti.oracle.com is a good start, list of books, performance tuning guide and reference

    http://download-west.oracle.com/docs...a96533/toc.htm

  8. #8
    Join Date
    Sep 2001
    Posts
    37
    Thank you very much to all who replied..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width