Oracle Database - Optimizer Goal (CBO/RBO)
About
- 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
Articles Related
CBO
Choosing a goal
The query optimizer determines the most efficient way to execute a SQL statement after considering many factors including the Optimizer Goal.
| Optimizer Goal | Description | ||
|---|---|---|---|
| best throughput best response time | The optimizer chooses the least amount of resources necessary to process | all rows the first row | accessed by a SQL statement |
By default, the goal of the query optimizer is the best throughput.
Choose a goal for the query optimizer based on the needs of your application:
- For system where the last result is most important, optimize for best throughput.
Example application performed in batch, such as data access tool applications. Response time is less important, because the user does not examine the results of individual statements while the application is running.
- For OLTP system where the user is waiting to see the first row such such as ERP, optimize for best response time.
Usually, response time is important in interactive applications, because the interactive user is waiting to see the first row or first few rows accessed by the statement.
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. |
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. |
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 Oracle Database - Implications of the 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!).
