Change value of OPTIMIZER MODE parameter in Oracle

Optimizer Mode Parameter in Oracle

Optimizer choose the execution plan according to the value of OPTIMIZER MODE parameter  for all rows it check all rows for session to choose better execution plan . It has different values you can set as you need.

Default value is ALL_ROWS

You can set is at session or system level for testing or permanent changes at database level.

First_rows_n Optimizer used a cost-based approach according to response time of the return the first n rows

--n stands for no of rows
Alter system set OPTIMIZER_MODE = first_rows_n scope=both;

--For first 1000 rows
Alter system set OPTIMIZER_MODE = first_rows_1000 scope=both;
-- For first 100 rows
Alter system set OPTIMIZER_MODE=first_rows_100 scope=both;
--For first one rows
Alter system set OPTIMIZER_MODE=first_rows_1 scope=both;

FIRST_ROWS: Optimizer choose mix of Cost and heuristics plan for better execution plan of first few rows

Alter system set OPTIMIZER_MODE = first_rows scope=both;

ALL_ROWS Optimizer used a cost-based approach for all SQL statements in the session and try best execution plan.

-- Default setting in Oracle
Alter system set OPTIMIZER_MODE=ALL_ROWS scope=both;

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.