The CPU Costing Model – A Few Thoughts Part II

As previously discussed, the formula used by the CBO using the CPU costing model is basically:
 
(sum of all the single block I/Os x average wait time for a single block I/O +
 sum of all the multiblock I/Os x average wait time for a multiblock I/O +
 sum of all the required CPU cycles / CPU cycles per second)
/
average wait time for a single block I/O
 

When determining the multiblock I/Os costs associated with a FTS, the CBO basically:
 
 – determines the number of multiblock operations (blocks in dba_tables / mbrc system statistic)
 
 – then multiplies this out by the average wait time of a multiblock I/O (mreadtim system statistic)
 
to determine the total wait time for all expected multiblock read operations.
 
  -This total wait time of all multiblock read operations is then finally divided by the average wait time for a single block I/O (sreadtim system statistic) to express the final cost in units of single block I/Os.
 
 
Remember these average wait times associated with both single and multiblock I/Os are actual wait times for these events as experienced in the specific database environment and captured during the collection of system statistics.
 
Therefore, the formula automatically takes into consideration and incorporates into the calculations any discrepancies and differences in wait times between a single and a multiblock I/O.
 
For example, if a multiblock I/O actually takes (say) 10ms to perform on average, while a single block I/O only takes (say) 5ms to perform on average, then the formula will automatically make the costs of performing multiblock reads to be twice as expensive as the costs associated with performing the single block reads as performed by index scans.
 
These discrepancies in costs and trying to make a level playing field when comparing the multiblock I/Os costs associated with FTS vs. the single block I/Os costs associated with index scan is precisely what the optimizer_index_cost_adj parameter was designed to addressed.
 
Rather than treat both types of I/Os as being the same, which is the default behaviour with the I/O costing model, the optimizer_index_cost_adj parameter is designed to adjust the single block read costs to ensure that they are indeed costed as being (say) 1/2 the cost as that of a typical multiblock I/O.
 
However, when using the CPU costing model, the optimizer_index_cost adj parameter is effectively redundant as the necessary adjustments are already incorporated into the final costs. The total time required to perform a multiblock read operation is divided by the time it takes on average to perform a single block read operation. Using the optimizer_index_cost_adj parameter, although supported and permissible, will likely result in the final CBO costs being adjusted inappropriately as the index related single block I/Os will “double-dip” and potentially reduce both as a result of the system statistic differences between sreadtim and mreadtim and also as a result of the optimizer_index_cost_adj parameter as well.
 
The system stats are much preferred provided they’re accurate and kept reasonably up to date, because one doesn’t need to “manually” change any associated database parameter.
 
Not only are the comparative differences between sreadtim and mreadtim maintained, but so are other useful system statistics such as the mbrc statistic to be discussed next.
 
So in summary, when using the CPU costing model, do not set the optimizer_index_cost_adj parameter at all. Leave it alone, collect representative system statistics and let the system statistics look after the comparative costs between single and multiblock I/Os for you automatically.