System Statistics 3

In a recent thread on one of the Oracle Forums, someone asked the question:

So, in general, if systems statistics are in effect, would you or would you not make any adjustments to the optimizer_index_cost_adj and optimizer_index_caching parameters?

Under what circumstance?

This is my reply:

With system statistics in place I would generally expect to leave those parameters to default.

After serious consideration of individual cases I would still expect to leave the optimizer_index_cost_adj to default in almost all cases, but I could be persuaded that the optimizer_index_caching needed to be changed. [Update: The only alternative is to modify the blevel on individual indexes – and I’m not very keen on doing that either]. [Updated again – many years later: 11.2.0.4 introduced (as a backport from 12c) the table_cached_blocks preference as a way of getting better costing for using indexes.]

Both parameters can be given a realistic “physical” meaning, and therefore are valid in some circumstances. The trouble is that they apply at a system level when you really need them to apply at the individual segment level (or even at the “segment when used in specific statement” level).

The optimum case for fiddling with the optimizer_index_caching is where you have some important large-scale queries that the optimizer sees as border-line between nested loop joins and other join mechanisms and therefore switches in an apparently random way between the options. If the nested loop option is obviously (to you) the better choice then setting the index caching parameter may be enough to stabilise plans for these queries without introducing changes, or instability, to the execution plans for other queries.

It is possible to change these parameters at the session level, by the way, so you can introduce variations on a production system by selective use of logon triggers if you really think it might help.

[More on System Statistics]