DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: cost base vs rule base optimization

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227

    cost base vs rule base optimization

    Hi,

    what is the performance impact of no statistics if optimizer_mode is choose? I know that the optimizer will default to rule base, but is that bad for the db. And why?


    Thanks in advance!
    leonard905
    leonard905@yahoo.com

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Rule based optimization is an older method that the optimizer can use to figure out how to execute your query. The Cost based optimizer is a more "intelligent" optimizer that looks at statistics to determine how to execute your query.

    When you set your optimizer goal to CHOOSE, the optimizer will by default use the CBO unless you don't have statistics. When running in CBO mode, it is very important you have accurate statistics on your database (ANALYZE TABLE, or DBMS_STATS.Gather*). Most of the new 8i query features (FGAC,Function Based Indexes, etc.) will only work with the CBO.
    Jeff Hunter

  3. #3
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    But what is the impact if any if you run on rule base.
    leonard905
    leonard905@yahoo.com

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The "Impact" is your queries might not run as fast or you can't use the new features. Will that affect a plain-old OLTP database with poorly written queries? Probably not.
    Jeff Hunter

  5. #5
    Join Date
    Oct 2002
    Posts
    182
    Originally posted by marist89
    The "Impact" is your queries might not run as fast or you can't use the new features. Will that affect a plain-old OLTP database with poorly written queries? Probably not.
    lol
    - Cookies

  6. #6
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    http://tahiti.oracle.com

    concepts guide
    I'm stmontgo and I approve of this message

  7. #7
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    for the difference between cbo and rule based, see the concepts guide.

    It also depends on the application that is running against your db.
    If the application is written with rbo in mind, it's beter not to change it.

    If you choose to use cbo, don't forget to analyse your schema's.

    HTH
    Gert

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