Re: Oracle does not use an index

sybrandb_at_yahoo.com wrote:
> The default value of these parameters are woefully inadequate.
> The setting for the second parameter assumes indexes are never present
> in cache.
> This is simply not true.
> My defaults are 40 for cost_adj and 90 for caching.

Jonathan Lewis made a strong case for system statistics instead of these two parameters as a better first approach to getting the CBO to make better judgements about the cost of queries at UKOUG. You can read an earlier article explaining this at
http://www.oracle.com/technology/pub/articles/lewis_cbo.html . My summary (though probably a gross simplification) is that not only is the new CPU costing model a better model for costing query plans, but also that it allows Oracle to understand directly what your hardware really performs like (how slow the disk are on average for example), so you are using a better model with more relevant information, rather than making a set of assumptions.

A point that was made at the conference, but not in the earlier article, was that the OICA and OIC parameter changes tended to make costs smaller overall and the effect of rounding errors in the calculation becomes more significant (perhaps enhancing the chance of poor decisions) whereas system statistics tend to make costs relatively larger and so tend not to suffer so much from this effect.

As an aside, though it is true that the default assumption that indexes are not cached is clearly wrong, what tends to get left out is the assumption that the cbo makes that *all* ios will be a physical IO. Tables get cached as well. Adjusting assumptions for one sort of object but not others may not be entirely sensible in all cases.

Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com Received on Fri Nov 04 2005 - 09:59:27 CST