Playing with Optimizer Mode - Execution Plan

Today i need to talk about optimizer  modes in oracle and in this post i will specially talk about ALL_ROWS and FIRST_ROWS modes.

Possbile values can be:

OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.
Values:
first_rows_n
The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).
first_rows
The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
all_rows
The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).
Lets create a test table and index over it with statistics:

SQL> create table opt_test  as select * from dba_objects;
Table created.

SQL> create index opt_testindex  on opt_test(OBJECT_TYPE);
Index created.

SQL> exec dbms_stats.gather_table_stats('sys','opt_test');
PL/SQL procedure successfully completed.

SQL> select count(*) from opt_test;
  COUNT(*)
----------
     62952

SQL> set pagesize 200
SQL> set linesize 200
SQL> select object_type,count(*) from dba_objects group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP               5
INDEX PARTITION            228
SEQUENCE                   314
QUEUE                       21
SCHEDULE                     1
TABLE PARTITION            557
RULE                         1
JAVA DATA                  301
PROCEDURE                  283
OPERATOR                    57
LOB PARTITION                1
WINDOW                       2
DATABASE LINK                1
LOB                        679
PACKAGE                    983
PACKAGE BODY               921
LIBRARY                    154
RULE SET                    11
PROGRAM                     12
TYPE BODY                  174
CONTEXT                      7
JAVA RESOURCE              773
XML SCHEMA                  24
TRIGGER                    435
JOB CLASS                    2
UNDEFINED                    6
DIRECTORY                   18
MATERIALIZED VIEW            5
TABLE                     4163
INDEX                     4524
SYNONYM                  24462
VIEW                      4977
FUNCTION                   342
WINDOW GROUP                 1
JAVA CLASS               16474
INDEXTYPE                   10
JAVA SOURCE                  5
CLUSTER                     10
TYPE                      1990
RESOURCE PLAN                3
EVALUATION CONTEXT           8
JOB                          8
42 rows selected.

You we can say totaly table rows i.e. 63k , synonyms is in maximum quantity almost 25k. Now when we select objects with object_type as
synonyms it should go for full table scan rather than going for index scan than fetch data from the tables.
But things are not simple as it looks we can cheat around oracle using different optimizer mode as shown below:

SQL> set autotrace traceonly exp
SQL>  alter session set optimizer_mode=ALL_ROWS;
Session altered.
SQL> select * from opt_test where object_type='SYNONYM';
Execution Plan
----------------------------------------------------------
Plan hash value: 1427440082
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 23810 |  2185K|   197   (4)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| OPT_TEST | 23810 |  2185K|   197   (4)| 00:00:03 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_TYPE"='SYNONYM')
SQL> alter session set optimizer_mode=FIRST_ROWS;
Session altered.
SQL>  select * from opt_test where object_type='SYNONYM';
Execution Plan
----------------------------------------------------------
Plan hash value: 743984098
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               | 23810 |  2185K|  1133   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| OPT_TEST      | 23810 |  2185K|  1133   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | OPT_TESTINDEX | 23810 |       |    66   (2)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_TYPE"='SYNONYM')
SQL>

As we need first rows quickly optimizer compromises on cost of execution plan and chooses the plan which will deliver first rows fastly.

This does not declare your optimer mode is not good, but it is beneficial in OLTP environment where user need first rows fastly on their on screen , then plan is real good.


Conclusion
Cost based optimizer gives you flexibility to choose response time or throughput. So use them based on your business requirement.