RelationalDBDesign RelationalDBDesign

Optimizer Modes   «Prev 

Two optimizer modes: 1) cost, 2) rule

Two Optimizer Modes used when tuning Oracle SQL

  1. Oracle has two optimizer modes, cost and rule.
  2. The choose optimizer mode may use either rule-based or cost-based, depending upon the presence of statistics in the data dictionary.
  3. The system-wide default optimizer mode is controlled by the init.ora parameter called optimizer_mode.
  4. The optimizer_mode parameter may be set to first_rows, all_rows, rule, or choose.
  5. The default optimizer mode will only be used if there are no ALTER SESSION or SQL hints in your queries.
  6. All SQL that has been tuned should have a hint to ensure that the SQL will continue to perform, even if the default mode is changed.
  7. The default mode can be overridden with ALTER SESSION SET OPTIMIZER_GOAL or by the use of SQL hints.
  8. SQL hints are placed inside comments, immediately after the SELECT statement.
  9. Because the hints reside inside comments, a syntax error will never cause an error message.

Query Optimizer Modes

The enhanced query optimizer has two modes:
  1. Normal mode
  2. Tuning mode

Normal mode

In normal mode, the optimizer compiles the SQL and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode the optimizer operates with very strict time constraints, usually a fraction of a second, during which it must find a good execution plan.

Tuning mode

In tuning mode, the optimizer performs additional analysis to check whether the execution plan produced under normal mode can be further improved. The output of the query optimizer is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly superior plan. When called under the tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer. The tuning performed by the Automatic Tuning Optimizer is called Automatic SQL Tuning. Under tuning mode, the optimizer can take several minutes to tune a single statement. It is both time and resource intensive to invoke the Automatic Tuning Optimizer every time a query has to be hard-parsed. The Automatic Tuning Optimizer is meant to be used for complex and high-load SQL statements that have non-trivial impact on the entire system. The Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements which are good candidates for Automatic SQL Tuning.