Rules base optimazer

jortiz · #1 (**permalink**) 09-30-04, 16:51

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 !!!

borzoi313 · #2 (**permalink**) 09-30-04, 18:15

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

jortiz · #3 (**permalink**) 09-30-04, 18:38

How do I set It? which parameters do I have to change? and what values do I have to Put ?

RBARAER · #4 (**permalink**) 10-01-04, 06:22

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 :

Quote:

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

shoblock · #5 (**permalink**) 10-01-04, 12:42

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).