Oracle Database - Optimizer Goal (CBO/RBO)

Card Puncher Data Processing

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





Discover More
Relational Algebra Between Sql And Query Plan
Nested Loop - Driving Table

The key to the performance of a NESTED LOOPS join is the order in which the tables are joined. The selection of the driving table, the first table in the join, is critical. The amount of repetition in...
Obia Powercenter Topology
OBIA - Installation Version 7.9.6 with EBS, PowerCenter, Oracle Database on Windows

Roadmap To install and set up Oracle BI Applications, do the following: preinstallation steps for the source...
Card Puncher Data Processing
Oracle - Controlling the Behavior of the Query Optimizer with initialization parameters

This section lists some initialization parameters that can be used to control the behaviour of the query optimizer. These parameters can be used to enable various optimizer features in order to improve...
Sql Developer Autotrace
Oracle Database - Autotrace

Autotrace is: a system variable of sql plus can also be found in SQL Developer AUTOTRACE provides you: an execution plan (such as explain plan) and some important regarding its actual execution....
Card Puncher Data Processing
Oracle Database - FIRST_ROWS

FIRST_ROWS is an optimizer goal in order to ask it the best response time. You can instruct this instruction through: a hint or by setting the value of the optimizer goal In the two case, it instructs...
Query Plan For The Same Query Response Time
Sql Engine - (Physical|Execution) Plan

A physical plan (or execution plan) is an ordered tree of physical relational operator that represent the execution a query A logical plan is also a ordered tree of relational operator but without the...



Share this page:
Follow us:
Task Runner