Re: optimizer parameters
From: Tim Gorman <tim_at_evdbt.com>
Date: Sun, 24 Apr 2011 11:20:54 -0600
Message-ID: <4DB45BF6.8010907_at_evdbt.com>
Your instincts are right on -- leave these parameters alone, use system statistics. Modifying those parameters to fix a localized problem such as a "problem query" is rather like amputating your head because you have acne -- sure it gets rid of the acne but can cause a few other problems.
Suggest to the vendor that they need to solve the specific problem without jeopardizing the rest of the application(s) using the database. A specific change for a specific problem, not a global change for a specific problem.
On the other hand, we've all been here. The vendor is likely to dig in their heels, demand that the parameters be changed, ignore your well-reasoned advice, and your management will override you because legal advised them to, and that's that. Just the way it is... ...can you see that this weekend hasn't been nearly long enough? :-)
Hope this helps...
-Tim
On 4/24/2011 6:22 AM, ed lewis wrote:
Date: Sun, 24 Apr 2011 11:20:54 -0600
Message-ID: <4DB45BF6.8010907_at_evdbt.com>
Ed,
Your instincts are right on -- leave these parameters alone, use system statistics. Modifying those parameters to fix a localized problem such as a "problem query" is rather like amputating your head because you have acne -- sure it gets rid of the acne but can cause a few other problems.
Suggest to the vendor that they need to solve the specific problem without jeopardizing the rest of the application(s) using the database. A specific change for a specific problem, not a global change for a specific problem.
On the other hand, we've all been here. The vendor is likely to dig in their heels, demand that the parameters be changed, ignore your well-reasoned advice, and your management will override you because legal advised them to, and that's that. Just the way it is... ...can you see that this weekend hasn't been nearly long enough? :-)
Hope this helps...
-Tim
On 4/24/2011 6:22 AM, ed lewis wrote:
-- http://www.freelists.org/webpage/oracle-l Received on Sun Apr 24 2011 - 12:20:54 CDTHi,I'm curious what other peoples's experience, suggestionsare in regard to the use of the "optimizer_index_caching", and"optimizer_index_cost_adj" parameters.As a general practice, I leave them at their defaults, unIessthe vendor specifically requires that they be modifed. I usesystem statistics instead. I haven't find the need to do otherwise.I've tested these parameters on queries, and have received various results.In some cases, it made use of an index, and in other cases,it had no impact.I'm not a fan of changing this in midstream for an appthat has been running for almost 2 years. This particular vendoris asking to modify this, as a possible solution to a slow-runningquery. I'm pushing back, and would rather address this in otherways, such as tracing the query, and go from there.Your input is appreciated.edoracle 10.2.0.4solaris 10rac, asm 10.2.0.4