Re: optimizer parameters
Date: Tue, 26 Apr 2011 12:41:07 +0100
Message-ID: <BANLkTi=rvWQZ-xdOjVzweMdcMNOJd_hRqA_at_mail.gmail.com>
Historically (8i and earlier) those two parameters were made available to alter the cost of execution plans to make index access 'cheaper'. They did this, broadly, by *reducing *the cost calculations for certain index access paths. Prior to 9i this was pretty much the only way to make the CBO artificially favour indexed plans. Whether this was smart or not is debateable, but it did become popular (largely because it and other system wide tuning changes via parameter setting tended to appear on page 1 of google searches at that time). The introduction of system statistics (which can have similar effects but does it by increasing calculated costs thus avoiding some of the rounding side effects of the earlier approach) and the cpu costing model has arguably made the earlier approach obsolete, but once an approach becomes popular it takes forever to die!
On Tue, Apr 26, 2011 at 11:05 AM, ed lewis <eglewis71_at_gmail.com> wrote:
> Hi Dave,
> Yes, I can trace the query in both environments,
> and compare the results.
>
> I also doubt that the vendor will push to get these parameters
> implemented, knowing that they will be accountable.
> In the meantime, I plan to go forward, and handle each
> sql query on an individual basis.
> I'm still curious though, if anyone has modified, and implemented
> these parameters. If you were installing a new application, would
> modifying these parameters be a general practice, even if it was
> not a vendor recommendation ?
>
> thanks
> ed
>
> ----- Original Message ----- From: "David Mann" <dmann99_at_gmail.com>
> To: <oracle-l_at_freelists.org>
> Sent: Monday, April 25, 2011 11:06 AM
> Subject: Re: optimizer parameters
>
>
>
> >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-l
>>
>>
>> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- Niall Litchfield Oracle DBA http://www.orawin.info -- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 26 2011 - 06:41:07 CDT