Re: optimizer parameters

> 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? :-)

I beg to disagree. I once had a problem with a query that was taking a very long time on a development database with relatively low volumes compared to the 4 minutes it was taking on the production database (three times bigger). The DBAs were instructed to do what they could to have identical plans in both cases. I rewrote the query, and demonstrated that it could get the result in 2 or 3 seconds in dev - and that by the way it was taking now 30s in prod. Sent the test-case back, never heard of them after that.

SF

>
> On 4/24/2011 6:22 AM, ed lewis wrote:
>> Hi,
>> I'm curious what other peoples's experience, suggestions
>> are 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, unIess
>> the vendor specifically requires that they be modifed. I use
>> system 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 app
>> that has been running for almost 2 years. This particular vendor
>> is asking to modify this, as a possible solution to a slow-running
>> query. I'm pushing back, and would rather address this in other
>> ways, such as tracing the query, and go from there.
>> Your input is appreciated.
>> ed
>> oracle 10.2.0.4
>> solaris 10
>> rac, asm 10.2.0.4
> -- http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l