Optimizer Modes   «Prev  Next»

SQL Optimizer Modes

Here are the correct matches:
  1. Statistics: Information about the nature of table and index data
  2. Heuristics: A set of general rules that are used to determine table access
  3. ANALYZE: Used to create Oracle table and index statistics
  4. Choose: A flexible optimizer mode

Query Optimizer and Execution Plans

When a SQL statement is executed on an Oracle database, the Oracle query optimizer determines the most efficient execution plan after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can greatly affect execution time.
During the evaluation process, the query optimizer reviews statistics gathered on the system to determine the best data access path and other considerations. You can override the execution plan of the query optimizer with hints inserted in SQL statement.

You can change the goal of the query optimizer for all SQL statements in a session by changing the parameter value in initialization file or by the 
ALTER SESSION SET
OPTIMIZER_MODE
statement. For example:
  1. The following statement in an initialization parameter file establishes the goal of the query optimizer for all sessions of the instance to best response time: OPTIMIZER_MODE = FIRST_ROWS_1
  2. The following SQL statement changes the goal of the query optimizer for the current session to best response time: ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;
If the optimizer uses the cost-based approach for a SQL statement, and if some tables accessed by the statement have no statistics, then the optimizer uses internal information, such as the number of data blocks allocated to these tables, to estimate other statistics for these tables.

OPTIMIZER_MODE Initialization Parameter

The OPTIMIZER_MODE initialization parameter establishes the default behavior for choosing an optimization approach for the instance.
The possible values and description are listed in Table 6-7.
OPTIMIZER_MODE Initialization Parameter Values
Table 6-7: OPTIMIZER_MODE Initialization Parameter Values
You can change the goal of the query optimizer for all SQL statements in a session by changing the parameter value in initialization file or by the 
ALTER SESSION SET OPTIMIZER_MODE
statement. For example:
  1. The following statement in an initialization parameter file establishes the goal of the query optimizer for all sessions of the instance to best response time: 
    OPTIMIZER_MODE = FIRST_ROWS_1
  2. The following SQL statement changes the goal of the query optimizer for the current session to best response time: 
    ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;
If the optimizer uses the cost-based approach for a SQL statement, and if some tables accessed by the statement have no statistics, then the optimizer uses internal information, such as the number of data blocks allocated to these tables, to estimate other statistics for these tables.