OPTIMIZER_MODE

Parameter type	String
Syntax	OPTIMIZER_MODE = {first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | choose | rule}
Default value	choose
Parameter class	Dynamic: ALTER SESSION

OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.

Values:

• rule

  The optimizer chooses a rule-based approach for all SQL statements regardless of the presence of statistics.

• choose

  The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available.

  If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput. If the data dictionary contains only some statistics, then the cost-based approach is used, and the optimizer must guess the statistics for the subjects without any statistics. This can result in sub-optimal execution plans. If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.

• first_rows_n

  The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).

• first_rows

  The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

• all_rows

  The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

  See Also: Oracle9i Database Performance Tuning Guide and Reference for more information on setting this parameter Oracle9i Database Concepts and Oracle9i Database Performance Tuning Guide and Reference for more information about the optimizer