This content has been marked as final. Show 18 replies
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.
Both parameters can be given a real "physical" meaning, and therefore are valid in some circumstances; the trouble is that they apply at a system level, you really need them to apply at a segment level (or even "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 are borderline between hash joins and nested loops and the optimizer switches in an apparently random way between the two options. If the nested loop option is (to you) obviously the better choice, then setting the index caching parameter may be enough to stabilise plans for these queries without introducing a changes or instability to other execution plans.
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.
Thanks! That is largely the conclusion we've come to based on limited testing.
It appears that changing the parameters in ALTER SESSION does not affect a query with an already existing plan; I have yet to see any change based on doing this, but they've all been with queries with existing plans, and in most cases, on systems that did not have system statistics in place.
The biggest difference we've seen in adding system statistics is with/without appropriate table statistics. Adding system statistics to a system and testing on a schema that did not have object statistics resulted in performance as good as having both object statistics and system statistics (but I wouldn't generalize this too far, as it is a pretty limited test case).
But is very clear that not using systems statistics is just plain foolish...
what did you do to enable system statistics? I am trying to get more familiar with the pros/cons of generating system stats. i have tested a problem query by using the optimizer_index_cost_adj parameter (via alter session) and see a response time benefit. but I keep reading plenty of people advising against setting that parameter. i am just trying to get more familiar with system statistics and the proper way to use them.