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 SQL Engine - Query Optimizer (Query Optimization)
- In 10g and above, it is based on cbo
Articles Related
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. |