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
statement. For example:
- 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
- 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.