Choosing an Optimizer Goal

By default, the goal of the query optimizer is the best throughput. This means that it chooses the least amount of resources necessary to process all rows accessed by the statement. Oracle can also optimize a statement with the goal of best response time. This means that it uses the least amount of resources necessary to process the first row accessed by a SQL statement.

You always choose a goal for the optimizer based on the needs of your application from two options.

For, Oracle Reports applications throughput is more important ,because the user initiating the application is only concerned with the time necessary for the application to complete. Response time is less important, because the user does not examine the results of individual statements while the application is running.

For Oracle Forms applications or SQL*Plus queries response time is important because the interactive user is waiting to see the first row or first few rows accessed by the statement.

The goal of the optimizer is affected by,

A)OPTIMIZER_MODE Initialization Parameter
B)Optimizer SQL Hints for Changing the Query Optimizer Goal
C)Query Optimizer Statistics in the Data Dictionary

A)OPTIMIZER_MODE Initialization Parameter
---------------------------------------------------------------------------
The OPTIMIZER_MODE initialization parameter establishes the default behavior for choosing an optimization approach for the instance. To know your current settings issue,

SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS

This parameter can have three types of values.
1)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.

2)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.

3)FIRST_ROWS: The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows. FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.

B)Optimizer SQL Hints for Changing the Query Optimizer Goal
-------------------------------------------------------------------------------------------
To specify the goal of the query optimizer for an individual SQL statement, use one of the hints from FIRST_ROWS(n) or ALL_ROWS. Actually hints in an individual SQL statement override the OPTIMIZER_MODE initialization parameter for that SQL statement.

C)Query Optimizer Statistics in the Data Dictionary
---------------------------------------------------------------------------------------
The statistics used by the query optimizer are stored in the data dictionary. If no statistics are available when using query optimization, the optimizer will do dynamic sampling depending on the setting of the OPTMIZER_DYNAMIC_SAMPLING initialization parameter. This may cause slower parse times so for best performance, the optimizer should have representative optimizer statistics.