Oracle's Optimizer_index_caching and Optimizer_index_cost_adj parameters are the most
important parameters among all the init.ora (SPFILE) parameters. The Cost based optimizer
extensively uses these 2 parameters values during the cost calculation for arriving the optimum
execution plan.
OPTIMIZER_INDEX_CACHINGThis parameter controls the costing of an index probe in conjunction with a nested loop. The range of
values 0 to 100 for OPTIMIZER_INDEX_CACHING indicates percentage of index blocks in the buffer
cache, which modifies the optimizer's assumptions about index caching for nested loops and IN-list
iterators. A value of 100 infers that 100% of the index blocks are likely to be found in the buffer cache
and the optimizer adjusts the cost of an index probe or nested loop accordingly. Use caution when using
this parameter because execution plans can change in favor of index caching.
OPTIMIZER_INDEX_COST_ADJ
This parameter can be used to adjust the cost of index probes. The range of values is 1 to 10000. The
default value is 100, which means that indexes are evaluated as an access path based on the normal
costing model. A value of 10 means that the cost of an index access path is one-tenth the normal cost
of an index access path.
It is very easy to understand the impact of the first parameter, OPTIMIZER_INDEX_CACHING. It
tells us that percentage of index blocks in the buffer cache affect the cost calculation.
However, the 2nd parameter, OPTIMIZER_INDEX_COST_ADJ confuses many DBAs and developers
because the range of values accepted is 1 through 10000. Many experts interpret the meaning in
a different way. Any change in OPTIMIZER_INDEX_COST_ADJ results in table access cost being
scaled down (up) to the current value divided by 100.
In other words, I would say, if the value is less than 100, then the Optimizer may choose indexed
access path OR if the value is equal to (greater than 100), then Optimizer may choose FULL TABLE
SCAN access path.
You can also interpret the meaning in this way: less value of OPTIMIZER_INDEX_COST_ADJ
indicates single block read time is cheaper; more value of OPTIMIZER_INDEX_COST_ADJ indicates
multi block read time is cheaper.
Optimizer_Index_Cost_Adj values …………………………………………………………………..>
1….10..20..30……..………………………..100…200…..500……1000…4000….8000……10000
<Consider Index Access …> <Consider FULL TABLE SCAN ………………………>
Among the 2 parameters, Optimizer_Index_Cost_Adj is more aggressive than
OPIMIZER_INDEX_CACHING.Note that all tests were conducted on 9iR2 where the system statistics was not collected.
First let us set up 2 tables to test the theory.
SQL> desc sales
Name Null? Type
----------------- -------- ------------------
CUST_ID NOT NULL NUMBER(12)
STATE NOT NULL CHAR(2)
SALE_YEAR NUMBER(4)
SALE_MONTH NUMBER(2)
PROD_ID NUMBER
QTY NUMBER
AMT NUMBER
COMM_1 VARCHAR2(50)
COMM_2 VARCHAR2(200)
SQL> select /*+ full(a) parallel(a,16) */ count(*) from sales a ;
COUNT(*)
----------
14,336,064 -----------à Big table to test different values.
SQL> desc state
Name Null? Type
--------------------- -------- ------------------
STATEID NOT NULL CHAR(2)
NAME VARCHAR2(30)
SOMETXT VARCHAR2(3900)
9 rows there are in the table.
Case 1
Let us first see what will be the cost for a query with default values of 0 and 100 for
optimizer_index_caching and optimizer_index_cost_adj respectively.
SQL> alter session set optimizer_index_caching = 0;
Session altered.
SQL> alter session set optimizer_index_cost_adj = 100 ;
Session altered.
SQL> explain plan for
2 select a.cust_id , b.name
3 from sales a, state b
4 where a.state = b.stateid and
5 b.stateid = 'GA' ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 573K | 15M | 45551 |
| 1 | NESTED LOOPS | | 573K | 15M | 45551 |
| 2 | TABLE ACCESS BY INDEX ROWID | STATE | 1 | 21 | 1 |
|* 3 | INDEX UNIQUE SCAN | STATE_PK | 1 | | |
|* 4 | TABLE ACCESS FULL | SALES | 573K | 4480K | 45550 |
------------------------------------------------------------------------------------------------------
So, the total cost of the query is 45,551 IO. Note also that when CPU costing is off, then the COST
is nothing but total number of IO.
This is our base line data that will be compared with the remaining tests.Case 2
I changed the OPTIMIZER_INDEX_COST_ADJ to 10 but kept the “default” value for
OPTIMIZER_INDEX_CACHING.
SQL> alter session set optimizer_index_caching = 0 ;
Session altered.
SQL> alter session set optimizer_index_cost_adj = 10 ;
Session altered.
SQL> explain plan for
2 select a.cust_id , b.name
3 from sales a, state b
4 where a.state = b.stateid and
5 b.stateid = 'GA' ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 573K| 15M | 37683 (1) |
| 1 | NESTED LOOPS | | 573K| 15M | 37683 (1) |
| 2 | TABLE ACCESS BY INDEX ROWID | STATE | 1 | 21 | 2 (50) |
|* 3 | INDEX UNIQUE SCAN | STATE_PK | 1 | | |
| 4 | TABLE ACCESS BY INDEX ROWID | SALES | 573K | 4480K | 37682 (1) |
|* 5 | INDEX RANGE SCAN | SALES_IDX_2 | 573K | | 30095 (0) |
----------------------------------------------------------------------------------------------------------------
The Query plan is completely changed – from full table scan to indexed access on the SALES table.
The total cost of the query came down to 37,683 from 45,551, the “TABLE ACCESS BY INDEX
ROWID” cost is 37,682 that includes the cost of “ INDEX RANGE SCAN “ for SALES_IDX_2 30,095.Case 3
In real life situation some amount of index leaf blocks are always cached in the memory.
Now I tell Oracle that 50 % of index blocks are cached in the SGA by changing the
OPTIMIZER_INDEX_CACHING parameter.
Let us study the cost of the query.
SQL> alter session set optimizer_index_caching = 50 ;
Session altered.
SQL> alter session set optimizer_index_cost_adj = 10 ;
Session altered.
SQL> explain plan for
2 select a.cust_id , b.name
3 from sales a, state b
4 where a.state = b.stateid and
5 b.stateid = 'GA' ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 573K| 15M| 36207 (0) |
| 1 | NESTED LOOPS | | 573K| 15M| 36207 (0) |
| 2 | TABLE ACCESS BY INDEX ROWID | STATE | 1 | 21 | 2 (50) |
|* 3 | INDEX UNIQUE SCAN | STATE_PK | 1 | | |
| 4 | TABLE ACCESS BY INDEX ROWID | SALES | 573K| 4480K| 36206 (0) |
|* 5 | INDEX RANGE SCAN | SALES_IDX_2 | 573K| | 15344 (0) |
--------------------------------------------------------------------------------------------------------------
In Case -2 , the “INDEX RANGE SCAN” for SALES_IDX_2 was 30095, after changing the parameter
OPTIMIZER_INDEX_CACHING to 50, it came to 15344, just half of what it was before.
The “TABLE ACCESS BY INDEX ROWID” for SALES table came down to 36206 from 37682.
Finally the total cost came down to 36,207 from 37,683.Case 4
If you further increase the parameter OPTIMIZER_INDEX_CACHING, then the cost of INDEX SCAN
will further come down.
Let us test this concept with a new value, 90.
SQL> alter session set optimizer_index_caching = 90 ;
Session altered.
SQL> alter session set optimizer_index_cost_adj = 10 ;
Session altered.
SQL> explain plan for
2 select a.cust_id , b.name
3 from sales a, state b
4 where a.state = b.stateid and
5 b.stateid = 'GA' ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 573K| 15M | 34980 (0)|
| 1 | NESTED LOOPS | | 573K| 15M | 34980 (0)|
| 2 | TABLE ACCESS BY INDEX ROWID | STATE | 1 | 21 | 2 (50) |
|* 3 | INDEX UNIQUE SCAN | STATE_PK | 1 | | |
| 4 | TABLE ACCESS BY INDEX ROWID | SALES | 573K| 4480K| 34979 (0)|
|* 5 | INDEX RANGE SCAN | SALES_IDX_2 | 573K| | 3069 (0) |
--------------------------------------------------------------------------------------------------------
The cost of INDEX RANGE SCAN has been reduced to 3069.
In conclusion
One thing you should remember, the above tests and various costs obtained did not tell you which
query will run faster.
I just demonstrated the impact of changing those 2 parameters.
There is no rule of thumb to fix those 2 values. How ever, One thing is sure that the default value
0 and 100 for OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ respectively are set
for Data Warehouse System.
If your system is OLTP, then these 2 parameters values should be changed. I would test the
system with different values before choosing the correct values.
Setting 90 to OPTIMIZER_INDEX_CACHING and 10 (or 15) to OPTIMIZER_INDEX_COST_ADJ will
perform good for OLTP system.
If you collect system statistics, then these 2 parameters play less importance role in deciding the
execution plan.
important parameters among all the init.ora (SPFILE) parameters. The Cost based optimizer
extensively uses these 2 parameters values during the cost calculation for arriving the optimum
execution plan.
OPTIMIZER_INDEX_CACHINGThis parameter controls the costing of an index probe in conjunction with a nested loop. The range of
values 0 to 100 for OPTIMIZER_INDEX_CACHING indicates percentage of index blocks in the buffer
cache, which modifies the optimizer's assumptions about index caching for nested loops and IN-list
iterators. A value of 100 infers that 100% of the index blocks are likely to be found in the buffer cache
and the optimizer adjusts the cost of an index probe or nested loop accordingly. Use caution when using
this parameter because execution plans can change in favor of index caching.
OPTIMIZER_INDEX_COST_ADJ
This parameter can be used to adjust the cost of index probes. The range of values is 1 to 10000. The
default value is 100, which means that indexes are evaluated as an access path based on the normal
costing model. A value of 10 means that the cost of an index access path is one-tenth the normal cost
of an index access path.
It is very easy to understand the impact of the first parameter, OPTIMIZER_INDEX_CACHING. It
tells us that percentage of index blocks in the buffer cache affect the cost calculation.
However, the 2nd parameter, OPTIMIZER_INDEX_COST_ADJ confuses many DBAs and developers
because the range of values accepted is 1 through 10000. Many experts interpret the meaning in
a different way. Any change in OPTIMIZER_INDEX_COST_ADJ results in table access cost being
scaled down (up) to the current value divided by 100.
In other words, I would say, if the value is less than 100, then the Optimizer may choose indexed
access path OR if the value is equal to (greater than 100), then Optimizer may choose FULL TABLE
SCAN access path.
You can also interpret the meaning in this way: less value of OPTIMIZER_INDEX_COST_ADJ
indicates single block read time is cheaper; more value of OPTIMIZER_INDEX_COST_ADJ indicates
multi block read time is cheaper.
Optimizer_Index_Cost_Adj values …………………………………………………………………..>
1….10..20..30……..………………………..100…200…..500……1000…4000….8000……10000
<Consider Index Access …> <Consider FULL TABLE SCAN ………………………>
Among the 2 parameters, Optimizer_Index_Cost_Adj is more aggressive than
OPIMIZER_INDEX_CACHING.Note that all tests were conducted on 9iR2 where the system statistics was not collected.
First let us set up 2 tables to test the theory.
SQL> desc sales
Name Null? Type
----------------- -------- ------------------
CUST_ID NOT NULL NUMBER(12)
STATE NOT NULL CHAR(2)
SALE_YEAR NUMBER(4)
SALE_MONTH NUMBER(2)
PROD_ID NUMBER
QTY NUMBER
AMT NUMBER
COMM_1 VARCHAR2(50)
COMM_2 VARCHAR2(200)
SQL> select /*+ full(a) parallel(a,16) */ count(*) from sales a ;
COUNT(*)
----------
14,336,064 -----------à Big table to test different values.
SQL> desc state
Name Null? Type
--------------------- -------- ------------------
STATEID NOT NULL CHAR(2)
NAME VARCHAR2(30)
SOMETXT VARCHAR2(3900)
9 rows there are in the table.
Case 1
Let us first see what will be the cost for a query with default values of 0 and 100 for
optimizer_index_caching and optimizer_index_cost_adj respectively.
SQL> alter session set optimizer_index_caching = 0;
Session altered.
SQL> alter session set optimizer_index_cost_adj = 100 ;
Session altered.
SQL> explain plan for
2 select a.cust_id , b.name
3 from sales a, state b
4 where a.state = b.stateid and
5 b.stateid = 'GA' ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 573K | 15M | 45551 |
| 1 | NESTED LOOPS | | 573K | 15M | 45551 |
| 2 | TABLE ACCESS BY INDEX ROWID | STATE | 1 | 21 | 1 |
|* 3 | INDEX UNIQUE SCAN | STATE_PK | 1 | | |
|* 4 | TABLE ACCESS FULL | SALES | 573K | 4480K | 45550 |
------------------------------------------------------------------------------------------------------
So, the total cost of the query is 45,551 IO. Note also that when CPU costing is off, then the COST
is nothing but total number of IO.
This is our base line data that will be compared with the remaining tests.Case 2
I changed the OPTIMIZER_INDEX_COST_ADJ to 10 but kept the “default” value for
OPTIMIZER_INDEX_CACHING.
SQL> alter session set optimizer_index_caching = 0 ;
Session altered.
SQL> alter session set optimizer_index_cost_adj = 10 ;
Session altered.
SQL> explain plan for
2 select a.cust_id , b.name
3 from sales a, state b
4 where a.state = b.stateid and
5 b.stateid = 'GA' ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 573K| 15M | 37683 (1) |
| 1 | NESTED LOOPS | | 573K| 15M | 37683 (1) |
| 2 | TABLE ACCESS BY INDEX ROWID | STATE | 1 | 21 | 2 (50) |
|* 3 | INDEX UNIQUE SCAN | STATE_PK | 1 | | |
| 4 | TABLE ACCESS BY INDEX ROWID | SALES | 573K | 4480K | 37682 (1) |
|* 5 | INDEX RANGE SCAN | SALES_IDX_2 | 573K | | 30095 (0) |
----------------------------------------------------------------------------------------------------------------
The Query plan is completely changed – from full table scan to indexed access on the SALES table.
The total cost of the query came down to 37,683 from 45,551, the “TABLE ACCESS BY INDEX
ROWID” cost is 37,682 that includes the cost of “ INDEX RANGE SCAN “ for SALES_IDX_2 30,095.Case 3
In real life situation some amount of index leaf blocks are always cached in the memory.
Now I tell Oracle that 50 % of index blocks are cached in the SGA by changing the
OPTIMIZER_INDEX_CACHING parameter.
Let us study the cost of the query.
SQL> alter session set optimizer_index_caching = 50 ;
Session altered.
SQL> alter session set optimizer_index_cost_adj = 10 ;
Session altered.
SQL> explain plan for
2 select a.cust_id , b.name
3 from sales a, state b
4 where a.state = b.stateid and
5 b.stateid = 'GA' ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 573K| 15M| 36207 (0) |
| 1 | NESTED LOOPS | | 573K| 15M| 36207 (0) |
| 2 | TABLE ACCESS BY INDEX ROWID | STATE | 1 | 21 | 2 (50) |
|* 3 | INDEX UNIQUE SCAN | STATE_PK | 1 | | |
| 4 | TABLE ACCESS BY INDEX ROWID | SALES | 573K| 4480K| 36206 (0) |
|* 5 | INDEX RANGE SCAN | SALES_IDX_2 | 573K| | 15344 (0) |
--------------------------------------------------------------------------------------------------------------
In Case -2 , the “INDEX RANGE SCAN” for SALES_IDX_2 was 30095, after changing the parameter
OPTIMIZER_INDEX_CACHING to 50, it came to 15344, just half of what it was before.
The “TABLE ACCESS BY INDEX ROWID” for SALES table came down to 36206 from 37682.
Finally the total cost came down to 36,207 from 37,683.Case 4
If you further increase the parameter OPTIMIZER_INDEX_CACHING, then the cost of INDEX SCAN
will further come down.
Let us test this concept with a new value, 90.
SQL> alter session set optimizer_index_caching = 90 ;
Session altered.
SQL> alter session set optimizer_index_cost_adj = 10 ;
Session altered.
SQL> explain plan for
2 select a.cust_id , b.name
3 from sales a, state b
4 where a.state = b.stateid and
5 b.stateid = 'GA' ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 573K| 15M | 34980 (0)|
| 1 | NESTED LOOPS | | 573K| 15M | 34980 (0)|
| 2 | TABLE ACCESS BY INDEX ROWID | STATE | 1 | 21 | 2 (50) |
|* 3 | INDEX UNIQUE SCAN | STATE_PK | 1 | | |
| 4 | TABLE ACCESS BY INDEX ROWID | SALES | 573K| 4480K| 34979 (0)|
|* 5 | INDEX RANGE SCAN | SALES_IDX_2 | 573K| | 3069 (0) |
--------------------------------------------------------------------------------------------------------
The cost of INDEX RANGE SCAN has been reduced to 3069.
In conclusion
One thing you should remember, the above tests and various costs obtained did not tell you which
query will run faster.
I just demonstrated the impact of changing those 2 parameters.
There is no rule of thumb to fix those 2 values. How ever, One thing is sure that the default value
0 and 100 for OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ respectively are set
for Data Warehouse System.
If your system is OLTP, then these 2 parameters values should be changed. I would test the
system with different values before choosing the correct values.
Setting 90 to OPTIMIZER_INDEX_CACHING and 10 (or 15) to OPTIMIZER_INDEX_COST_ADJ will
perform good for OLTP system.
If you collect system statistics, then these 2 parameters play less importance role in deciding the
execution plan.
No comments:
Post a Comment