Optimization Goals

Like most components within the database, the Optimizer is affected by the settings of certain database parameters. We will consider some of these within this section.

OPTIMIZER_MODE

The Optimizer can be directed to optimize SQL statement execution, and select execution plans, based upon stated goals. These goals may be specified at the following levels by means of the OPTIMIZER_MODE parameter.

• The instance
• The current session
• A specific SQL statement

The Optimizer mode or goal permits these values, each of which is explained in the table that follows:

OPTIMIZER_MODE = ALL_ROWS | FIRST_ROWS | FIRST_ROWS_x

Current Session Setting

An Optimizer goal may also be specified for the current session with the ALTER SESSION command. Note that the Optimizer goal will only affect user-defined SQL statements and not internal SQL processing initiated by the database. Furthermore, since it is only a goal, any recursive SQL statements generated internally by the database will use the instance Optimizer mode.