Checking the Optimizer Mode (Oracle) 

Starting with version 7 of Oracle, two optimizer modes are available. The Optimizer session is established using the init<SID>.ora-parameter optimizer_mode.

 

·        cost-based: init<SID>.ora parameter optimizer_mode = choose

·        rule-based: init<SID>.ora parameter optimizer_mode = rule

 

The parameter optimizer_mode is set for SAP systems and should only be changed if recommended by SAP. You should also take into account the appropriate notes.

In contrast to the rule-based optimizer, you should create statistical tables for the cost-based optimizer. If you do not regularly create these table statistics, this may cause the cost-based optimizer to make wrong decisions and result in performance problems.

 

See also:

Database Statistics with BR*Tools