The effect of optimizer_mode on SQL execution plans Oracle Database Tips by Donald BurlesonJuly 6, 2015

The effect of optimizer_mode on SQL execution plans

There are several "silver bullet" optimizer parameters, system-wide settings that have a profound impact on system-wide performance, most important being optimizer_mode, optimizer_index_caching and optimizer_index_cost_adj.   

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. 

Oracle recommends that you determine the "best" optimizer_mode based upon your specific optimizer goals, and measured response times (or resource consumptions) for your unique SQL workload.  This is done with the 11g Real Application Testing (RAT) tool, or with traditional benchmarking techniques to test the performance of your workload as a whole.

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

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;

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

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 and choose 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

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.

For more details on the powerful optimizer parameters in Oracle, see my book  "Oracle Tuning: The Definitive Reference".

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.