Optimizer Mode

What is Optimizer Mode

Optimizer is the mind of the Oracle engine and it decides the execution plan of the sql queries to be executed. Optimizer has different mode and Optimizer Mode in the parameter which is used to specify the different modes

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

SELECT * FROM V$PARAMETER WHERE NAME = 'optimizer_mode';
 or 
 show parameter optimizer_mode

Different Optimizer Nodes

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 oracle 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

How to change the Optimizer Mode

Optimizer mode can be changed at the session level or system level

For Session level change
 ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;
 For System level change:
 ALTER SYSTEM SET OPTIMIZER_MODE = FIRST_ROWS SCOPE=BOTH;

How to specify Optimizer Mode in the sql query

We can specify the optimizer mode while executing the queries using sql hints also

SELECT /*+ ALL_ROWS */  ID,ENAME
 SELECT /*+ FIRST_ROWS */ ID,ENAME
 SELECT /*+ RULE */ ID,ENAME

Also Read
Oracle Performance Tuning: Nice articles on Performance tuning
Oracle Performance Tuning Glossary : Glossary of All the oracle performance tuning terms