Re: optimizer parameters
From: David Mann <dmann99_at_gmail.com>
Date: Mon, 25 Apr 2011 11:06:20 -0400
Message-ID: <BANLkTinxTML66Tbr1DP+vcN1Aazuu_YEFw_at_mail.gmail.com>
>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.
Date: Mon, 25 Apr 2011 11:06:20 -0400
Message-ID: <BANLkTinxTML66Tbr1DP+vcN1Aazuu_YEFw_at_mail.gmail.com>
>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.
So the plans are the same - I would let the vendor know what you found - especially that their recommendation did not solve the issue. I avoid "Big Knob" tuning on established systems as well. Unless the client has a way to test the system and determine if any performance got worse because of the changes.
In the meantime anything stopping you from tracing execution (with WAIT info captured) on Dev and Prod to see differences in what the query is spending its time on?
-Dave
-- Dave Mann www.brainio.us www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml -- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 25 2011 - 10:06:20 CDT