Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    294

    Lightbulb Unanswered: Rules base optimazer

    Hello, I have an Oracle 9i database, with choose optimizer, and I want to set It in rules. How do I do It with commands and Using DB studio !!

    Thanks !!!

  2. #2
    Join Date
    Mar 2004
    Location
    Colorado
    Posts
    49
    In 9.2 the optimizer_mode is not listed as dynamic in the OEM. So you will need to set it in your init.ora (or spfile) whichever you are using and then bounce the database

  3. #3
    Join Date
    Jun 2003
    Posts
    294
    How do I set It? which parameters do I have to change? and what values do I have to Put ?

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    Just add in your initSID.ora :

    OPTIMIZER_MODE = RULE

    However, be aware that since 9i, Oracle strongly recommends to use the cost_based optimizer instead of the rule-based one. In Oracle 9i doc :

    Note: Oracle Corporation strongly advises the use of cost-based optimization. Rule-based optimization will be deprecated in a
    future release.
    If you just want to use the rule-based optimizer for some specific queries, you can just add hints to your queries to tell Oracle to do so :

    SELECT /*+ RULE */ ... FROM ...

    Regards,

    RBARAER

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    I'm a huge fan of RULE mode (have been since v5), but I'd have to recommend against it at this point. It doesn't work in 10, so if you're planning to upgrade anytime in the future, you'd be better off having all your code work under COST already. Not only is RULE no longer valid for init.ora in 10, but the RULE hint can actually make the same sql perform worse than it did on v9 with rule.

    Also, in 9 the CBO is finally starting to work as well as oracle has always claimed, and stats are gathered much faster - making it reasonable to gather them during the day after large processing (a problem in v8).
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

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