This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Dec 13, 2007 10:25 AM by dbtoo Go to original post RSS
  • 15. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    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.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk
  • 16. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    426411 Oracle ACE
    Currently Being Moderated
    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...
  • 17. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    567263 Newbie
    Currently Being Moderated
    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.

    thanks! tyler
  • 18. Re: System Statistics and optimizer_index_cost_adjust  interaction in 9i
    dbtoo Explorer
    Currently Being Moderated
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref842

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#CIHIEIIA
1 2 Previous Next