Originally posted by AJW_ID01

Is there some kind of logical/systematic approach we can bring to the usage of optimizer_index_cost_adj?

You have to know your data and how it is used. This parameter is well documented on tahiti.oracle.com and asktom.oracle.com.

I was pretty much thinking that it's a blatant "user_interventionist" approach to forcing index use, that can potentially depart us from full table scans altogether by forcing the optimizer away from them.

Force is such a harsh word. You're just giving the optimizer more information about your data which it can use to adjust its choices.


I've set it to a very marginal '90' at present, so at least the optimizer is getting a nudge in the right direction for index usage. I'm curious to know how everyone else approaches this parameter though. In my case, the slight intervention PLUS the downgrading of dbfmrd appears to turn the tide against FTS, but I'm unsure as to whether to take any further steps at this time.

Not sure I would have downgraded dfmbrc...



Is 25 a generically prudent choice?

For an OLTP system, yes. For a DW, probably not.


If so, why on earth does it default to 100?
Because the default settings are usually set for a "general purpose" database.