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