OPTIMIZER

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:

Oracle Database Performance Tuning Guide for more information on setting this parameter
Oracle Database Concepts and Oracle Database Performance Tuning Guide for more information about the optimizer