Richard Foote's Oracle Blog

OPTIMIZER_INDEX_CACHING Parameter September 1, 2009

As previously discussed, the CBO assumes the actual costs and overheads associated with all I/Os to be the same, regardless of the type of I/O, unless told otherwise via the optimizer_index_cost_adj parameter.
 
Another key assumption the CBO makes by default is that all I/Os will be physical I/Os (PIO) and so be relatively expensive to perform and “worthy” of being costed.
 
However, this of course is not always the case with many of the blocks being requested and accessed by Oracle already cached in the buffer cache. In specific scenarios, the CBO can take the likely caching characteristics of indexes into consideration and reduce the cost of an index related execution path accordingly. Note however this only applies for I/Os associated for index specific blocks in specific scenarios where the same index is repeatedly accessed.
 
For example, in the case of a nested loop join where the inner table is typically accessed via an index look-up, the same index may repeatedly access the table many times within the loop. Many of the blocks associated with this index are therefore quite likely to be cached as the index structure is being continually accessed. Same scenario for an index look-up process as a result of an IN list condition. For each element in the IN list, an index is often used to look-up the corresponding value in the table, thereby accessing the specific index again and again for each element in the IN list. As the index is continually being accessed, many of its associated blocks are likely to already be cached in memory.
 
The purpose of the optimizer_index_caching parameter is to tell the CBO what percentage of index related blocks are likely to already be cached in the buffer cache during these types of operations and so should not be considered in the overall costings associated with the index related execution path. The default is 0 which means by default Oracle doesn’t consider any index blocks to ever be cached and all I/Os associated with an index during an index access path need to treated as PIOs and costed accordingly. If however the optimizer_index_caching parameter is set to say 25, it means that the CBO will consider 25% of all I/Os associated directly with index blocks are likely to already be cached and will therefore reduce the overall cost of index block I/Os by 25%.
 
As discussed previously, the CBO I/O based costing formula is:
 
basic index range scan cost = index blevel + ceil(index selectivity x leaf blocks) + ceil(table selectivity x clustering factor)

The optimizer_index_caching parameter adjusts the formula in the following manner by reducing just the index accesses portion of the formula:

basic index range scan cost = ceil((index blevel + ceil(index selectivity x leaf blocks)) x (1- optimizer_index_caching)) + ceil(table selectivity x clustering factor)

but only for specific index scans such as nested loop joins and IN list conditions where an index is likely to be continually accessed within the same execution path.

So if we were to go back to the example I covered in the single predicate demo in the first CBO and Indexes Introduction post as shown below:

SQL> select * from bowie_stuff2 where id = 420;

2000 rows selected.

Execution Plan
———————————————————-
Plan hash value: 134336835
——————————————————————————
|Id| Operation                   | Name           | Rows  | Bytes | Cost  |
——————————————————————————
|0| SELECT STATEMENT            |                |  2000 | 36000 |    18 |
|1|  TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2   |  2000 | 36000 | 18 |
|*2|   INDEX RANGE SCAN          | BOWIE_STUFF2_I |  2000 |       |     9 |
——————————————————————————

we notice that the cost of the execution plan is 18.

If we now change the optimizer_index_caching parameter to say 75, meaning that 75% of all index blocks are now likely to be cached and rerun the query:
 
SQL> alter system set optimizer_index_caching=75;
 
System altered.
 
SQL> select * from bowie_stuff2 where id = 420;
 
2000 rows selected.
 

Execution Plan
———————————————————-
Plan hash value: 134336835
 
——————————————————————————
|Id| Operation                   | Name           | Rows  | Bytes | Cost  |
——————————————————————————
|0| SELECT STATEMENT            |                |  2000 | 36000 |    18 |
|1|  TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2   |  2000 | 36000 | 18 |
|*2|   INDEX RANGE SCAN          | BOWIE_STUFF2_I |  2000 |       |     9 |
——————————————————————————

we notice that the cost remains unchanged at 18 and parameter has had no effect, as the query was based on a single table equality predicate and did not have processing involving either a nest loop or IN list condition.
 
 
However, if we run the second IN list predicate demo involving an IN list condition as shown below (first resetting the optimizer_index_caching parameter back to 0):
 
 
SQL> alter system set optimizer_index_caching=0;
 
System altered.
 
SQL> SELECT * FROM bowie_stuff2 WHERE id in (20, 30, 420);
 
6000 rows selected.
 

Execution Plan
———————————————————-
Plan hash value: 2964430066
 
——————————————————————————-
|Id| Operation                    | Name           | Rows  | Bytes | Cost  |
——————————————————————————-
|0| SELECT STATEMENT             |                |  6000 |   105K|    49 |
|1|  INLIST ITERATOR             |                |       |       |       |
|2|   TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2   |  6000 |   105K| 49 |
|*3|    INDEX RANGE SCAN          | BOWIE_STUFF2_I |  6000 |       |    23 |
——————————————————————————-
 

We note we had a cost of 49. Remember, the cost of 49 was calculated in the following manner as we have 3 elements in the IN list condition:

cost = index blevel + ceil(index selectivity x leaf blocks) + ceil(table selectivity x clustering factor)

= 2 + 3 x ceil(0.01 x 602) + ceil(0.03 x 852)

= 2 + 3×7 + 26

= 2 + 21 + 26

= 23 + 26 = 49

If we now alter the optimizer_index_caching parameter to 75 and rerun the same IN list query:

SQL> alter system set optimizer_index_caching=75;
 
System altered.
 
SQL> SELECT * FROM bowie_stuff2 WHERE id in (20, 30, 420);
 
6000 rows selected.
 

Execution Plan
———————————————————-
Plan hash value: 2964430066
 
——————————————————————————-
|Id| Operation                    | Name           | Rows  | Bytes | Cost  |
——————————————————————————-
|0| SELECT STATEMENT             |                |  6000 |   105K|    32 |
|1|  INLIST ITERATOR             |                |       |       |       |
|2|   TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2   |  6000 |   105K| 32 |
|*3|    INDEX RANGE SCAN          | BOWIE_STUFF2_I |  6000 |       |     6 |
——————————————————————————-
 

 we notice the cost has been reduced from 49 down to 32. How has the optimizer_index_caching set to 75 changed the costs:
 

basic index range scan cost = ceil((index blevel + ceil(index selectivity x leaf blocks)) x (1- optimizer_index_caching)) + ceil(table selectivity x clustering factor)

= ceil((2 + 3 x ceil(0.01 x 602)) x (1-0.75)) + ceil(0.03 x 852)
 
= ceil((2 + (3×7)) x 0.25) + 26
 
= ceil((2 + 21) x 0.25)+ 26
 
= 6 + 26 = 32

 
So whereas previously there were 23 index block I/Os, this has been reduced down to just 6. Note that the I/Os and associated costs with accessing the actual table blocks within the index scan remains unaltered.

So how to set this parameter in a database ?  Well, there are a number of issues with it all.

Firstly, as with the optimizer_index_cost_adj parameter, there’s only the one “global” parameter (for the system or session) which means any value needs to be averaged out for all indexes and for all situations in which this parameter can have an effect. However, some indexes may for example be quite small and heavily accessed and as such quite likely to have most index blocks cached at any point in time (including leaf blocks) whereas other indexes may be quite huge and rarely and randomly accessed which means perhaps only the branch level blocks are likely to be cached even during a (say) IN list operation. As discussed previously, with all averages there will likely be examples where the value is appropriate, too high or too low depending the the characteristics of specific indexes.

Secondly, the poor table related blocks don’t have an equivalent parameter and so Oracle always assumes not only the table blocks within an index scan will be PIOs, but a FTS will only ever consist of PIOs, which conversely might not always be the case. So while we might make a reasonable guesstimate of the likelihood of an index block being cached (say via the buffer cache hit ratio, a study of the v$bh view, etc.), the CBO makes so such allowances for the possible caching characteristics of table related blocks. Yes, index blocks are more likely to be cached, especially during the specific scenarios in which the optimizer_index_caching parameter has an effect, but that doesn’t mean table blocks will always be PIOs. Therefore simply setting this parameter to what might appear a reasonable generalistic index caching value might still run the risk of favouring indexes unduly, even though it only impacts the index accessed blocks in the costing formula, as the CBO doesn’t make any such cost allowances for table blocks that might be cached in a FTS.

In the ideal world, we would have some idea of the caching characteristics of all individual indexes and tables and based on the segments being accessed and their associated caching characteristics, have the CBO make the necessary adjustments to it’s costing estimates in an execution path. Until we reach such an ideal world (which might not be that far away BTW), I basically recommend not to set this parameter at all and again simply ensure you use accurate system statistics and have accurate enough segment statistics.

I recommend setting this parameter if and when you find the CBO is commonly not choosing appropriate indexes for the above mentioned scenarios when perhaps it should and a slight “nudge” of costs in the right direction is sufficient to address the issues. The optimizer_index_caching parameter is not quite as overly “dangerous” if set incorrectly as the optimizer_index_cost_adj parameter can be, as it only impacts the “half” of the formula relating directly to index block I/Os and not the table block I/Os, which often constitute the greater proportion of overall I/Os in many index range scan operations (although as my example above shows, this depends as well).

However, with both of the optimizer_index parameters set, they can both have a hand in reducing the overall costs of an index related execution plan. The optimizer_index_caching parameter first impacts the cost of just the half of the formula relating to index block I/Os as shown above and then the optimizer_index_cost_adj parameter further impacts the overall resultant cost. So if we were to run the IN list query again, but this time also set the optimizer_index_cost_adj to say 25 as well as leaving the optimizer_index_caching to 75:

SQL> alter system set optimizer_index_cost_adj=25;

System altered.

SQL> SELECT * FROM bowie_stuff2 WHERE id in (20, 30, 420);

6000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 2964430066

——————————————————————————-
|Id| Operation                    | Name           | Rows  | Bytes | Cost  |
——————————————————————————-
|0| SELECT STATEMENT             |                |  6000 |   105K|     8 |
|1|  INLIST ITERATOR             |                |       |       |       |
|2|   TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2   |  6000 |   105K| 8 |
|*3|    INDEX RANGE SCAN          | BOWIE_STUFF2_I |  6000 |       |     2 |
——————————————————————————-

We note the the cost of the execution plan has further reduced down from 32 to just 8. Basically it’s just the previous cost of 32 x optimizer_index_cost_adj = 32 x 0.25 = 8.

However, rather than setting either of these parameters, I would simply recommend the appropriate use of system statistics and the CPU costing model as I’ll discuss later.