OPTIMIZER_MODE

Property Description
Parameter type String
Syntax OPTIMIZER_MODE =

{ first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows }
Default value all_rows
Modifiable ALTER SESSION, ALTER SYSTEM

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

Values:

  • first_rows_n

    The optimizer uses a cost-based approach 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 and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

See Also: