OPTIMIZER MODE

Oracle optimizer is a boon for making decisions related to execution plans or access paths of fetching the data from the disks in a more efficient way. Optimizer has the intelligence and capability to decide the plans with help of the statistics it feeds on from the data. In earlier days, oracle tend to use rule based execution plans to optimize the execution of SQL queries. But this mode of optimization was cool but had lot of limitations like rules defined. Oracle still keep this rule based optimizer because still if you see the SQL code, you would be able to see lot of rule hints and hence oracle keeps this as a backward compatibility. In recent versions, oracle uses CBO (cost based optimizer) which is more accurate in calculation of access paths based on the statistics gathered. This optimizer uses cost of CPU or IO to calculate the cardinality estimates for efficient execution plan.

Optimizer mode is the parameter which is set to influence a collection of SQL statement with optimal speed.

There are different types of optimizer modes in oracle.There is no such thing as cost parameter to set in optimizer_mode.

  • ALL_ROWS
  • FIRST_ROWS
  • FIRST_ROWS_N
  • RULE
  • CHOOSE

optimizer_mode=all_rows

This mode mostly reduces the resource cost or less amount of resource spent to complete a task . This mode can be used in Datawarehouse systems(OLAP), DSS and batch jobs systems. In this mode , we can always see full table scan with parallel operations. Because of the huge selectivity of data from the database, always less I/O is consumed.

70% – 100% of data is selected from the tables

Optimizer uses hash join for below query with three table joins using all_rows mode of optimizer. As we know that in all_rows mode optimizer always attracts full table scan with less resource consumption. But the response time of the query is very slow which is not always suitable for OLTP queries. But this mode will be helpful in batch jobs or when whole data needs to be scanned rather than subset of data where high throughput is required.

In the below output, look at the consistent gets and physical reads section. There are quite few logical and physical reads which indicates less resource intensive

SQL> set timing on
set autot traceonly
alter session set optimizer_mode=all_rows;

select a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
from test.SALES_TAB_COPY a
inner join test.tabx b on a.ORDER_ID=b.ORDER_ID
inner join test.sales_tab c on b.order_id=c.order_id;
group by a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
order by 1,2,3,4 desc;SQL> SQL>
Session altered.

Elapsed: 00:00:00.00
SQL> SQL>   2    3    4

1048576 rows selected.

Elapsed: 00:00:20.56

Execution Plan
----------------------------------------------------------
Plan hash value: 1312943110

--------------------------------------------------------------------------------
-----------------

| Id  | Operation              | Name           | Rows  | Bytes |TempSpc| Cost (
%CPU)| Time     |

--------------------------------------------------------------------------------
-----------------

|   0 | SELECT STATEMENT       |                |  1059K|    66M|       | 15085
  (1)| 00:00:01 |

|*  1 |  HASH JOIN             |                |  1059K|    66M|    40M| 15085
  (1)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL    | SALES_TAB_COPY |  1048K|    28M|       |  3628
  (1)| 00:00:01 |

|*  3 |   HASH JOIN            |                |  1053K|    38M|    17M|  6974
  (1)| 00:00:01 |

|*  4 |    INDEX FAST FULL SCAN| IDX_ID_X2      |  1048K|  5120K|       |   640
  (1)| 00:00:01 |

|   5 |    TABLE ACCESS FULL   | TABX           |  1048K|    33M|       |  3251
  (1)| 00:00:01 |

--------------------------------------------------------------------------------
-----------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."ORDER_ID"="B"."ORDER_ID")
   2 - filter("A"."ORDER_ID" IS NOT NULL)
   3 - access("B"."ORDER_ID"="C"."ORDER_ID")
   4 - filter("C"."ORDER_ID" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement


Statistics
----------------------------------------------------------
         78  recursive calls
          0  db block gets
      45472  consistent gets
       9462  physical reads
          0  redo size
   46475352  bytes sent via SQL*Net to client
     769506  bytes received via SQL*Net from client
      69907  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1048576  rows processed

optimizer_mode=first_rows_n

Both first_rows and first_rows_n are same parameters but first_rows was obsoleted from 10g oracle release.

However first_rows_n can be used with values like n=1,10,100,1000 to fetch the first n rows as early as possible using index scans . This mode is often used in Transaction processing system(OLTP)

Optimizer uses nested loops for below query with three table joins using first_rows mode of optimizer

In first_rows mode, the optimizer always attracts index scan with high resource consumption but the response time would be much faster compared to all_rows. This mode can be useful in OLTP queries which return subset of rows from million or zillion rows table where response time is much preferable.

Query optimizer generate the most optimal execution plan. CBO decides the optimum plan based on the lowest execution cost.

Cost of an execution plan includes following parameters

  • Selectivity (Object statistics) – Relative number
  • Cardinality (Object statistics) – Absolute number
  • Resource cost (CPU,I/O)

Selectivity:

Represents a fraction of rows from rowset(0-1)

  • 0 – no rows
  • 1 – all rows
  • Example – sales table column order_type has 100 rows and we select one order_id out of 100. Then selectivity is calculated as (1/100) = 0.01
select order_type from sales where order_id=369;

Cardinality:

Number of rows retrieved in each step in an execution plan. Cardinality is displayed under rows column of execution plan.

  • If selectivity of rows are low, then index scan is preferred most of time. It depends on various factors like table size, rows to be processed, storage of segments
  • If cardinality of rows are high , then full table scan is preferred most of time.
  • Example – If there are 369 customers require low order priority, then cardinality of the query is 369
select orders from customers where order_priority='L';

Sometimes optimizer underestimate or overestimate the cardinality which leads to suboptimal execution plan.

In the below output, look at the consistent gets and physical reads section. There are quite high logical and physical reads which indicates highly resource intensive due to index access.

SQL> set timing on
set autot traceonly
alter session set optimizer_mode=first_rows;

select a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
from test.SALES_TAB_COPY a
inner join test.tabx b on a.ORDER_ID=b.ORDER_ID
inner join test.sales_tab c on b.order_id=c.order_id;
group by a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
order by 1,2,3,4 desc;SQL>
SQL>
Session altered.

Elapsed: 00:00:00.00
SQL> SQL>   2    3    4


1048576 rows selected.

Elapsed: 00:00:31.81

Execution Plan
----------------------------------------------------------
Plan hash value: 2481604509

--------------------------------------------------------------------------------
------------------------

| Id  | Operation                             | Name           | Rows  | Bytes |
 Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
------------------------

|   0 | SELECT STATEMENT                      |                |  1013K|    63M|
  4828K  (1)| 00:03:09 |

|   1 |  NESTED LOOPS                         |                |  1013K|    63M|
  4828K  (1)| 00:03:09 |

|   2 |   NESTED LOOPS                        |                |  1008K|    58M|
  2810K  (1)| 00:01:50 |

|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| SALES_TAB_COPY |  1048K|    28M|
   710K  (1)| 00:00:28 |

|*  4 |     INDEX FULL SCAN                   | IDX_ID_X1      |  1048K|       |
  2349   (1)| 00:00:01 |

|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| TABX           |     1 |    33 |
     3   (0)| 00:00:01 |

|*  6 |     INDEX RANGE SCAN                  | IDX_ID_X       |     2 |       |
     2   (0)| 00:00:01 |

|*  7 |   INDEX RANGE SCAN                    | IDX_ID_X2      |     1 |     5 |
     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------
------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("A"."ORDER_ID" IS NOT NULL)
   6 - access("A"."ORDER_ID"="B"."ORDER_ID")
   7 - access("B"."ORDER_ID"="C"."ORDER_ID")
       filter("C"."ORDER_ID" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement


Statistics
----------------------------------------------------------
        176  recursive calls
          0  db block gets
    1145304  consistent gets
      31061  physical reads
          0  redo size
   45723245  bytes sent via SQL*Net to client
     769506  bytes received via SQL*Net from client
      69907  SQL*Net roundtrips to/from client
         20  sorts (memory)
          0  sorts (disk)
    1048576  rows processed

First_rows_n where n=10

Optimizer uses nested loops for below query with three table joins using first_rows_10 mode of optimizer

In the below output, look at the consistent gets and physical reads section. There are quite high logical and physical reads which indicates highly resource intensive due to index access but look at response time. The results are displayed faster.

SQL> set timing on
set autot traceonly
alter session set optimizer_mode=first_rows_10;

select a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
from test.SALES_TAB_COPY a
inner join test.tabx b on a.ORDER_ID=b.ORDER_ID
inner join test.sales_tab c on b.order_id=c.order_id;
group by a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
order by 1,2,3,4 desc;SQL> SQL>
Session altered.

Elapsed: 00:00:00.00
SQL> SQL>   2    3    4

1048576 rows selected.

Elapsed: 00:00:17.96

Execution Plan
----------------------------------------------------------
Plan hash value: 2238806332

--------------------------------------------------------------------------------
------------------------

| Id  | Operation                             | Name           | Rows  | Bytes |
 Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
------------------------

|   0 | SELECT STATEMENT                      |                |    11 |   726 |
    57   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                         |                |    11 |   726 |
    57   (0)| 00:00:01 |

|   2 |   NESTED LOOPS                        |                |    11 |   671 |
    35   (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL                  | TABX           |  1048K|    33M|
     2   (0)| 00:00:01 |

|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| SALES_TAB_COPY |     1 |    28 |
     3   (0)| 00:00:01 |

|*  5 |     INDEX RANGE SCAN                  | IDX_ID_X1      |     1 |       |
     2   (0)| 00:00:01 |

|*  6 |   INDEX RANGE SCAN                    | IDX_ID_X2      |     1 |     5 |
     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------
------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("A"."ORDER_ID"="B"."ORDER_ID")
       filter("A"."ORDER_ID" IS NOT NULL)
   6 - access("B"."ORDER_ID"="C"."ORDER_ID")
       filter("C"."ORDER_ID" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1145444  consistent gets
          0  physical reads
          0  redo size
   45723256  bytes sent via SQL*Net to client
     769506  bytes received via SQL*Net from client
      69907  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1048576  rows processed

First_rows_n where n=100

SQL> set timing on
set autot traceonly
alter session set optimizer_mode=first_rows_100;

select a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
from test.SALES_TAB_COPY a
inner join test.tabx b on a.ORDER_ID=b.ORDER_ID
inner join test.sales_tab c on b.order_id=c.order_id;
group by a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
order by 1,2,3,4 desc;SQL> SQL>
Session altered.

Elapsed: 00:00:00.00
SQL> SQL>   2    3    4

1048576 rows selected.

Elapsed: 00:00:18.32

Execution Plan
----------------------------------------------------------
Plan hash value: 4107626770

--------------------------------------------------------------------------------
---------------

| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%C
PU)| Time     |

--------------------------------------------------------------------------------
---------------

|   0 | SELECT STATEMENT             |                |   102 |  6732 |   510
(0)| 00:00:01 |

|   1 |  NESTED LOOPS                |                |   102 |  6732 |   510
(0)| 00:00:01 |

|   2 |   NESTED LOOPS               |                |   102 |  6732 |   510
(0)| 00:00:01 |

|   3 |    NESTED LOOPS              |                |   102 |  3876 |   204
(0)| 00:00:01 |

|   4 |     TABLE ACCESS FULL        | TABX           |  1048K|    33M|     2
(0)| 00:00:01 |

|*  5 |     INDEX RANGE SCAN         | IDX_ID_X2      |     1 |     5 |     2
(0)| 00:00:01 |

|*  6 |    INDEX RANGE SCAN          | IDX_ID_X1      |     1 |       |     2
(0)| 00:00:01 |

|   7 |   TABLE ACCESS BY INDEX ROWID| SALES_TAB_COPY |     1 |    28 |     3
(0)| 00:00:01 |

--------------------------------------------------------------------------------
---------------


Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("B"."ORDER_ID"="C"."ORDER_ID")
       filter("C"."ORDER_ID" IS NOT NULL)
   6 - access("A"."ORDER_ID"="B"."ORDER_ID")
       filter("A"."ORDER_ID" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1145444  consistent gets
          0  physical reads
          0  redo size
   45723256  bytes sent via SQL*Net to client
     769506  bytes received via SQL*Net from client
      69907  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1048576  rows processed

First_rows_n where n=1000

This mode of optimizer combines both nested loop and hash join together which is totally different plan compared to previous mode is it? As we given the value of n as 1000, the optimizer first scans the whole table blocks with full table scan and the scans the whole index blocks and again goes for index range scan to slice the index data.

SQL> set timing on
set autot traceonly
alter session set optimizer_mode=first_rows_1000;

select a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
from test.SALES_TAB_COPY a
inner join test.tabx b on a.ORDER_ID=b.ORDER_ID
inner join test.sales_tab c on b.order_id=c.order_id;
group by a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
order by 1,2,3,4 desc;SQL> SQL>
Session altered.

Elapsed: 00:00:00.00
SQL> SQL>   2    3    4

1048576 rows selected.

Elapsed: 00:00:18.29

Execution Plan
----------------------------------------------------------
Plan hash value: 1256037945

--------------------------------------------------------------------------------
-----------------------

| Id  | Operation                    | Name           | Rows  | Bytes |TempSpc|
Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
-----------------------

|   0 | SELECT STATEMENT             |                |  1001 | 99099 |       |
 4483   (1)| 00:00:01 |

|   1 |  NESTED LOOPS                |                |  1001 | 99099 |       |
 4483   (1)| 00:00:01 |

|   2 |   NESTED LOOPS               |                |  1001 | 99099 |       |
 4483   (1)| 00:00:01 |

|*  3 |    HASH JOIN                 |                |   996 | 70716 |    17M|
 1494   (1)| 00:00:01 |

|*  4 |     INDEX FAST FULL SCAN     | IDX_ID_X2      |  1048K|  5120K|       |
  640   (1)| 00:00:01 |

|   5 |     TABLE ACCESS FULL        | TABX           |  1048K|    33M|       |
    5   (0)| 00:00:01 |

|*  6 |    INDEX RANGE SCAN          | IDX_ID_X1      |     1 |       |       |
    2   (0)| 00:00:01 |

|   7 |   TABLE ACCESS BY INDEX ROWID| SALES_TAB_COPY |     1 |    28 |       |
    3   (0)| 00:00:01 |

--------------------------------------------------------------------------------
-----------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"."ORDER_ID"="C"."ORDER_ID")
   4 - filter("C"."ORDER_ID" IS NOT NULL)
   6 - access("A"."ORDER_ID"="B"."ORDER_ID")
       filter("A"."ORDER_ID" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1145444  consistent gets
          0  physical reads
          0  redo size
   45723256  bytes sent via SQL*Net to client
     769506  bytes received via SQL*Net from client
      69907  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1048576  rows processed

optimizer_mode= rule

Rule based optimizer is a stoneage optimizer mode which uses rule to generate the execution plan. Here the optimizer uses suboptimal index based on the low selectivity criteria. Rule mode doesnot uses statistics to generate the plan because oracle stopped updating for longer period. But in todays database systems, we often see rule hints in some of the EBS systems as part of SQL packages.

Optimizer uses again use nested loops for below query with three table joins using rule mode of optimizer. This resembles or imitates first_rows mode in terms of behaviour.

SQL> set timing on
set autot traceonly
alter session set optimizer_mode=rule;

select a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
from test.SALES_TAB_COPY a
inner join test.tabx b on a.ORDER_ID=b.ORDER_ID
inner join test.sales_tab c on b.order_id=c.order_id;
group by a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
order by 1,2,3,4 desc;SQL> SQL>
Session altered.

Elapsed: 00:00:00.00
SQL> SQL>   2    3    4

1048576 rows selected.

Elapsed: 00:00:19.56

Execution Plan
----------------------------------------------------------
Plan hash value: 3356042169

---------------------------------------------------------
| Id  | Operation                      | Name           |
---------------------------------------------------------
|   0 | SELECT STATEMENT               |                |
|   1 |  NESTED LOOPS                  |                |
|   2 |   NESTED LOOPS                 |                |
|   3 |    NESTED LOOPS                |                |
|   4 |     TABLE ACCESS FULL          | SALES_TAB      |
|   5 |     TABLE ACCESS BY INDEX ROWID| TABX           |
|*  6 |      INDEX RANGE SCAN          | IDX_ID_X       |
|*  7 |    INDEX RANGE SCAN            | IDX_ID_X1      |
|   8 |   TABLE ACCESS BY INDEX ROWID  | SALES_TAB_COPY |
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("B"."ORDER_ID"="C"."ORDER_ID")
   7 - access("A"."ORDER_ID"="B"."ORDER_ID")

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1503724  consistent gets
      13185  physical reads
          0  redo size
   45715607  bytes sent via SQL*Net to client
     769506  bytes received via SQL*Net from client
      69907  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1048576  rows processed

optimizer_mode= choose

Choose mode is an obsolete parameter where oracle has stopped the update of further enhancements. This mode was used once as an optimizer directive which switches between rule and choose based on the statistics.

Optimizer uses again use hash join for below query with three table joins using choose mode of optimizer. This resembles or imitates all_rows mode in terms of behaviour.

SQL> set timing on
set autot traceonly
alter session set optimizer_mode=choose;

select a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
from test.SALES_TAB_COPY a
inner join test.tabx b on a.ORDER_ID=b.ORDER_ID
inner join test.sales_tab c on b.order_id=c.order_id;
group by a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
order by 1,2,3,4 desc;SQL> SQL>
Session altered.

Elapsed: 00:00:00.00
SQL> SQL>   2    3    4

1048576 rows selected.

Elapsed: 00:00:16.83

Execution Plan
----------------------------------------------------------
Plan hash value: 1312943110

--------------------------------------------------------------------------------
-----------------

| Id  | Operation              | Name           | Rows  | Bytes |TempSpc| Cost (
%CPU)| Time     |

--------------------------------------------------------------------------------
-----------------

|   0 | SELECT STATEMENT       |                |  1059K|    66M|       | 15085
  (1)| 00:00:01 |

|*  1 |  HASH JOIN             |                |  1059K|    66M|    40M| 15085
  (1)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL    | SALES_TAB_COPY |  1048K|    28M|       |  3628
  (1)| 00:00:01 |

|*  3 |   HASH JOIN            |                |  1053K|    38M|    17M|  6974
  (1)| 00:00:01 |

|*  4 |    INDEX FAST FULL SCAN| IDX_ID_X2      |  1048K|  5120K|       |   640
  (1)| 00:00:01 |

|   5 |    TABLE ACCESS FULL   | TABX           |  1048K|    33M|       |  3251
  (1)| 00:00:01 |

--------------------------------------------------------------------------------
-----------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."ORDER_ID"="B"."ORDER_ID")
   2 - filter("A"."ORDER_ID" IS NOT NULL)
   3 - access("B"."ORDER_ID"="C"."ORDER_ID")
   4 - filter("C"."ORDER_ID" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement


Statistics
----------------------------------------------------------
         78  recursive calls
          0  db block gets
      45472  consistent gets
       8463  physical reads
          0  redo size
   46475352  bytes sent via SQL*Net to client
     769506  bytes received via SQL*Net from client
      69907  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1048576  rows processed

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