Understanding the Oracle Optimizer Modes

By Richard Niemiec on April 20, 2013

The Oracle optimizer was built to make your tuning life easier by choosing better paths for your poorly written queries. Rule-based optimization (now obsolete and unsupported) was built on a set of rules for how Oracle processes statements. Oracle 10g Release 2 only supported the use of the cost-based optimizer; the rule-based optimizer was no longer supported. Since Oracle 10g Release 2, Oracle has automatic statistics gathering turned on to aid the effectiveness of the cost-based optimizer. In Oracle, many features are only available when using cost-based optimization.

The cost-based optimizer now has two modes of operation: normal mode and tuning mode. Normal mode should be used in production and test environments; tuning mode can be used in development environments to aid developers and DBAs in testing specific SQL code.

How Optimization Looks at the Data

Rule-based optimization is Oracle-centric, whereas cost-based optimization is data-centric. The optimizer mode under which the database operates is set via the initialization parameter OPTIMIZER_MODE. The possible optimizer modes are as follows:

  • ALL_ROWS Gets all rows faster (generally forces index suppression). This is good for untuned, high-volume batch systems. This is the default.
  • FIRST_ROWS Gets the first row faster (generally forces index use). This is good for untuned systems that process lots of single transactions.
  • FIRST_ROWS (1|10|100|1000) Gets the first n rows faster. This is good for applications that routinely display partial results to users such as paging data to a user in a web application.
  • CHOOSE Now obsolete and unsupported but still allowed. Uses cost-based optimization for all analyzed tables. This is a good mode for well-built and well-tuned systems (for advanced users). This option is not documented for 11gR2 but is still usable.
  • RULE Now obsolete and unsupported but still allowed. Always uses rule-based optimization. If you are still using this, you need to start using cost-based optimization, as rule-based optimization is no longer supported under Oracle 10g Release 2 and higher.
  • The default optimizer mode for Oracle 11g Release 2 is ALL_ROWS. Also, cost-based optimization is used even if the tables are not analyzed. Although RULE/CHOOSE are definitely desupported and obsolete and people are often scolded for even talking about it, I was able to set the mode to RULE in 11gR2. Consider the following error I received when I set OPTIMIZER_MODE to a mode that doesn’t exist (SUPER_FAST):








The optimizer in Oracle 11g Release 2 uses cost-based optimization regardless of whether the tables have been analyzed or not.


There is no OPTIMIZER MODE called COST (a misconception). If you are using Oracle Database 9i Release 2 or an earlier version and are not sure what optimizer mode to use, then use CHOOSE or FIRST_ROWS and analyze all tables. As the data in a table changes, tables need to be reanalyzed at regular intervals. In Oracle 11g Release 2, the OPTIMIZER_MODES to use are ALL_ROWS and FIRST_ROWS.





Related Posts

Leave a Reply