[Updated 28th November 2011] – just after the fifth anniversary – to mark this as the first post of the Oracle Scratchpad.
A recent post on one of the OTN Database General forum pages asked about the effect of having the parameter optimizer_index_cost_adj set when you enable system statistics (also known as CPU costing).
I was quite surprised to see that I hadn’t mentioned this in my book (Cost Based Oracle Fundamentals) – even though there was a note in Chapter 4 comparing the effects of system statistics with the use of this parameter. So here’s a brief answer.
Remember that optimizer_index_cost_adj is just a number that gets used in the arithmetic for calculating a cost. It still gets used (apparently in exactly the same way) even if you have enabled system statistics.
Historically people used to give an informal description of the parameter as “this is the percentage of the cost of a multi-block read that should be given to a single block read when reading by an indexed access path”. But when you enable system statistics, it no longer makes sense to use this informal description, since part of the purpose of the system statistics is to show Oracle exactly how much longer a multi-block read takes compared to a single block read. So you probably need a new informal description – which is this:
When system statistics are enabled, for values up to 100 the parameter optimizer_index_cost_adj indicates the percentage of table blocks that are NOT likely to be in the cache when read through an indexed access path.
Like the earlier informal definition, this one suffers from a number of inaccuracies when you get into the details – but it is a reasonable statement that gives the right impression of the impact on the optimizer’s cost calculations of setting the parameter.