Table of Contents

About

SQL Engine - Query Optimizer Goal in Oracle

CBO

OPTIMIZER_MODE Initialization

The OPTIMIZER_MODE initialization parameter establishes the default behaviour for choosing an optimization approach for the instance. The possible values and description are listed in Table 11-2.

Value Description
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). This is the default value.
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 number of rows; n can equal 1, 10, 100, or 1000.
FIRST_ROWS The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.

Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.

You can change the goal of the query optimizer :

  • for the instance in the initialization file 
OPTIMIZER_MODE = FIRST_ROWS_1
  • for a session by altering it
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;
  • for a statement with an hint.

Hints for Changing the Query Optimizer Goal

Hint Description
FIRST_ROWS(n) This hint instructs Oracle to optimize an individual SQL statement with a goal of best response time to return the first n number of rows, where n equals any positive integer. The hint uses a cost-based approach for the SQL statement, regardless of the presence of statistic.
ALL_ROWS This hint explicitly chooses the cost-based approach to optimize a SQL statement with a goal of best throughput.

Documentation / Reference