Oracle Database - Optimizer Goal (CBO/RBO)

> Database > Oracle Database

1 - About

SQL Engine - Query Optimizer Goal in Oracle

  • In Oracle 9i and before the query plan generated by the Oracle database were determined by certain rules RBO
  • In 10g and above, it is based on CBO
Advertising

3 - CBO

3.1 - 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.
Advertising

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

4 - RBO

The RBO (Rule based Optimizer) is sensitive to the order of the tables in the FROM clause and will use the order in which we enter them to choose a Nested Loop - Driving Table for the query if none of the predicates do so (in the event of a “tie”, the RBO will look at the order the developer typed in table names to pick what table to use first!).

5 - Documentation / Reference