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

Basic

No

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.

    FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.

  • 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: