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).

Note:

1. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter

where upper(name) = ‘OPTIMIZER_MODE’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
optimizer_mode	ALL_ROWS	TRUE	TRUE	IMMEDIATE	TRUE	FALSE	optimizer mode

Oracle initializatoin parameters