Oracle Optimizer Modes (All_Rows, First_Rows, Choose & Rule RBO)

The optimizer_mode is the most powerful of these silver bullet parameters and a change can radically alter the characteristics of your SQL execution workload. 

To see how important the optimizer_mode is to SQL execution, consider the following three-way table join which returns 100,000 rows:

Let’s run this SQL query with several different optimizer_mode values and observe the changes to the execution plans and SQL response times:

select
    p.pat_first_name,
    p.Pat_last_name,
    v.arrive_dt_tm,
    v.depart_dt_tm,
    r.test_name,
    r.result_val,
    r.result_dt_tm
from
   patient p,
   pat_visit v,
   pat_result r
where
   p.pat_id=v.pat_id
and
   v.visit_id=r.visit_id;

ALL_ROWS:

With the default optimizer_mode of all_rows, we see that this query performs two full-scan operations against the target tables and feeds these into a hash join.  This is consistent with the optimizer goal of all_rows, which is to minimize computing resources.  Remember, index access involves additional I/O:

alter session set optimizer_mode=all_rows;

ID    PID    Operation               Name        Rows    Bytes    Cost   CPU Cost  IO Cost    Temp space
0        SELECT STATEMENT                         29M    2432M    153683     19G    152495    
1    0      HASH JOIN                             29M    2432M    153683     19G    152495     309M
2    1        HASH JOIN                         4990K     252M     16033      2G     15874     43M
3    2          TABLE ACCESS FULL   PATIENT     1000K      32M      1754    235M      1740    
4    2          TABLE ACCESS FULL   PAT_VISIT   5000K      90M      4705      1G      4635    
5    1        TABLE ACCESS FULL     PAT_RESULT    30M     944M     58074      9G     57500    

Statistics
---------------------------------------------------------- 
    149 recursive calls 
      0 db block gets 
  30841 consistent gets 
  10988 physical reads 
      0 redo size 
3848972 bytes sent via SQL*Net to client 
  73672 bytes received via SQL*Net from client 
   6668 SQL*Net roundtrips to/from client 
      0 sorts (memory) 
      0 sorts (disk) 
 100000 rows processed

FIRST_ROWS:

When we change the optimizer_mode to first_rows, we see that the execution plan changes, and the hash join is replaced by a nested loops join operation, and the full table scans are replaced by index range scans.  The first_rows access incurs additional I/O (more than the full scans in the all_rows plan), but the index access ensures the fastest possible response time:

alter session set optimizer_mode=first_rows;

ID    PID    Operation                        Name          Rows    Bytes       Cost   CPU Cost    IO Cost
0        SELECT STATEMENT                                    29M    2432M        46M    355G        46M
1    0      NESTED LOOPS                             
2    1        NESTED LOOPS                                   29M    2432M        46M    355G        46M
3    2          NESTED LOOPS                               4990K     252M    7004942     52G    7001775
4    3            TABLE ACCESS FULL           PATIENT      1000K      32M       1754    235M       1740
5    3            TABLE ACCESS BY INDEX ROWID PAT_VISIT        5       95          7   52750          7
6    5              INDEX RANGE SCAN          XIE1PAT_VISIT             5          2   16093          2
7    2          INDEX RANGE SCAN              XIE1PAT_RESULT            6          2   16293          2
8    1        TABLE ACCESS BY INDEX ROWID     PAT_RESULT       6      198          8   60642          8

FIRST_ROWS_100:

When we change the optimizer_mode to first_rows_100 the plan changes again, to a plan identical to the all_rows optimizer_mode:

alter session set optimizer_mode=first_rows_100;

ID    PID    Operation               Name       Rows    Bytes    Cost    CPU Cost    IO Cost
0        SELECT STATEMENT                        29M    2432M    153683      19G    152495
1    0      HASH JOIN                            29M    2432M    153683      19G    152495
2    1        HASH JOIN                        4990K     252M     16033       2G     15874
3    2          TABLE ACCESS FULL     PATIENT  1000K      32M      1754     235M      1740
4    2          TABLE ACCESS FULL   PAT_VISIT  5000K      90M      4705       1G      4635
5    1        TABLE ACCESS FULL    PAT_RESULT    30M     944M     58074      9G      57500

RULE:

Using the oldest optimizer_mode, the RULE hint, we see that the optimizer has chosen a different index from the first_rows plan.  This is because the rule-based optimizer (the RBO) does not have access to metadata statistics (from dbms_stats) and commonly chooses a sub-optimal index (an index with less selectivity).

alter session set optimizer_mode=rule;

ID  PID  Operation                                 Name
0        SELECT STATEMENT   
1    0      NESTED LOOPS   
2    1        NESTED LOOPS   
3    2          NESTED LOOPS   
4    3            TABLE ACCESS FULL                PAT_RESULT
5    3            TABLE ACCESS BY INDEX ROWID       PAT_VISIT
6    5              INDEX UNIQUE SCAN            XPKPAT_VISIT
7    2          INDEX UNIQUE SCAN                  XPKPATIENT
8    1        TABLE ACCESS BY INDEX ROWID             PATIENT

Statistics
---------------------------------------------------------- 
      0 recursive calls 
      0 db block gets 
 247417 consistent gets 
    874 physical reads 
      0 redo size 
3769059 bytes sent via SQL*Net to client 
  73672 bytes received via SQL*Net from client 
   6668 SQL*Net roundtrips to/from client 
      0 sorts (memory) 
      0 sorts (disk) 
 100000 rows processed

CHOOSE:

Using the obsolete optimizer_mode=choose, we see the execution plan return to the all_rows plan.  This is because the choose mode switches between rule andchoose depending upon the presence of optimizer statistics (from dbms_stats).

alter session set optimizer_mode=choose;

ID    PID    Operation               Name          Rows    Bytes    Cost    CPU Cost    IO Cost
0        SELECT STATEMENT                           29M    2432M    153683     19G     152495
1    0      HASH JOIN                               29M    2432M    153683     19G     152495
2    1        HASH JOIN                           4990K     252M     16033      2G      15874
3    2          TABLE ACCESS FULL      PATIENT    1000K      32M      1754    235M       1740
4    2          TABLE ACCESS FULL    PAT_VISIT    5000K      90M      4705      1G       4635
5    1        TABLE ACCESS FULL     PAT_RESULT      30M     944M     58074      9G      57500 

Now let’s review the amount of consistent gets that were required to service this query.  Remember, a consistent get is a logical I/O, a buffer read, and a well-tuned SQL statement will fetch the desired rows with a minimum amount of consistent gets:

Optimizer mode       Consistent gets

All_rows                                   30,841

First_rows                               177,164 

First_rows_100                       177,164 

Rule                                         247,417 

As we see, the value for optimizer_mode has a profound impact on the resulting SQL execution plans.  Hence, the "best" optimizer_mode for your specific workload should be tested and chosen prior to performing any specific tuning of SQL statements.

Courtesy: ASK TOM..