Re: optimizer parameters
- From: "ed lewis" <eglewis71@xxxxxxxxx>
- To: "Stephane Faroult" <sfaroult@xxxxxxxxxxxx>, <tim@xxxxxxxxx>
- Date: Mon, 25 Apr 2011 06:49:37 -0400
Stephane,
Thanks for you input.
I did change these parameters on the session level,
but it did not change the original plan for this
particular query. So even making these changes,
does not guarantee the desired or expected results.
As been mentioned, the focus should be on the
actual query.
Also, this particular query takes 14 seconds on prod,
but 6 seconds in dev. They both generate the same plan.
The environments also differ in data volume and user
activity. The vendor is questioning why the times are
not comparable.
ed
----- Original Message -----
From: Stephane Faroult
To: tim@xxxxxxxxx
Cc: eglewis71@xxxxxxxxx ; oracle-l
Sent: Sunday, April 24, 2011 1:49 PM
Subject: Re: optimizer parameters
Tim,
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
-- //www.freelists.org/webpage/oracle-l
Other related posts:
