Optimizer Mode

Set in the Init.Ora
Can be changed via ALTER SESSION
CHOOSE, FIRST_ROWS, ALL_ROWS = cost based optimization
RULE = rule based optimization

Optimizer_Mode = Rule
Original optimizer developed by prior to 7
Applications prior to 11i must run as RULE to be supported
Documentation has warned of its removal
uses strict rules for determining the execution path
Sensitive to position of table in FROM clause. Driving “starting” table should be listed last

Cost Based Optimization
Introduced in 7
favored optimizer approach
sensitive to data volume
Chooses the plan with the least cost where cost is a relative index of resources needed to execute operation

FIRST_ROWS Optimizer Mode
If an index scan is available, optimizer may choose it over a full table scan
If an index scan is available, optimizer may choose a nested loop join over a sort-merge join whenever the associated table is the potential inner table of the nested loop join.
If an index scan is made available by an ORDER BY clause, the optimizer may choose it to avoid a sort operation

ALL_ROWS Optimizer Mode
goal is to choose the minimum resource cost to return entire result set

CHOOSE Optimizer Mode
IF statistics have NOT been gathered on any table in the SQL statement then the optimizer reverts to RULE based optimization
IF statistics HAVE been gathered on at least ONE table in the SQL statement then the optimizer uses the ALL_ROWS cost based optimization approach