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):
NOTE
The optimizer in Oracle 11g Release 2 uses cost-based optimization regardless of whether the tables have been analyzed or not.
TIP
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.
Leave a Reply