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;