Re: optimizer parameters

An emphatic "no".

I'm guilty of writing a paper about 11 years ago after testing these parameters on Oracle8i (8.1.5 thru 8.1.7) and examining their role using 10053 traces which explained how they made the optimizer more aware of the system on which the database resided. Specifically, how O_I_C was intended to provide the optimizer some guidance on cache rates and how O_I_C_A was intended to give some idea of the relative speed/throughput of the different types of I/O Oracle performs. I did not think to annotate that paper to restrict its recommendations to Oracle8i until 2006, long after much damage had been done. With Oracle9i and the advent of system statistics and improvements to the optimizer, these parameters lost much of their value. They still quite obviously affect optimizer behavior, but there are better ways to accomplish the goal for which they were intended, and I look forward to a future release when they have been deprecated and then obsolesced.

So, my earlier analogy (i.e. amputating one's head to cure acne) is somewhat apt, not only to illustrate a global change for a specific problem with probable negative side-effects, but also in illustrating a relatively medieval solution for which much more modern and more effective solutions exist.

To answer your question directly, yes I have attempted to implement different values for these parameters in Oracle9i, and have seen others attempt to implement different values in Oracle10g, but in both cases I witnessed aberrant behavior by the optimizer, sometimes on the SQL statements for whom the modifications were originally intended, but most often as sudden issues with completely different SQL statements which were previously well-behaved. Restoring the parameters to their default values and digging further into the underlying problems, usually involving lack of gathered CBO statistics or a poor plan for gathering CBO statistics, including system statistics, was generally found to be the culprit. Implementing a solution for gathering CBO statistics when stale usuallly improved the situation globally.

Hope this helps...

Tim Gorman

consultant ->  Evergreen Database Technologies, Inc.
postal     =>  13087 West Cedar Drive #225, Lakewood CO 80228
website    =>  http://www.EvDBT.com/
email      =>  Tim_at_EvDBT.com
mobile     =>  +1-303-885-4526
fax        =>  +1-303-484-3608
Lost Data? =>  http://www.ora600.be/ for info about DUDE...

On 4/26/2011 4:05 AM, ed lewis 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
>
>
>
>

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