Changes to the Oracle 11g Optimizer That Affect Tuning

The general SQL tuning principles remain the same in 11g, but some significant optimizer changes should be noted.

• The RULE (and CHOOSE) OPTIMIZER_MODE has been deprecated and desupported in 11g. (The only way to get rule-based behavior in 11g is by using the RULE hint in a query, which is not supported either). In general, using the RULE hint is not recommended, but for individual queries that need it, it is there. Consult with Oracle support before using the RULE hint in 11g.
• In 11g, the cost-based optimizer has two modes: NORMAL and TUNING.
• In NORMAL mode, the cost-based optimizer considers a very small subset of possible execution plans to determine which one to choose. The number of plans considered is far smaller than in past versions of the database in order to keep the time to generate the execution plan within strict limits. SQL profiles (statistical information) can be used to influence which plans are considered.
• The TUNING mode of the cost-based optimizer can be used to perform more detailed analysis of SQL statements and make recommendations for actions to be taken and for auxiliary statistics to be accepted into a SQL profile for later use when running under NORMAL mode. TUNING mode is also known as the Automatic Tuning Optimizer mode, and the optimizer can take several minutes for a single statement (good for testing). See the Oracle Database Performance Tuning Guide Automatic SQL Tuning (Chapter 17 in the 11.2 docs).

Oracle states that the NORMAL mode should provide an acceptable execution path for most SQL statements. SQL statements that do not perform well in NORMAL mode may be tuned in TUNING mode for later use in NORMAL mode. This should provide a better performance balance for queries that have defined SQL profiles, with the majority of the optimizer work for complex queries being performed in TUNING mode once, rather than repeatedly, each time the SQL statement is parsed.