Re: "Depreciated" Parameters In SPFILE

From: Bob Jones <email_at_me.not>
Date: Thu, 16 Aug 2007 20:59:24 -0500
Message-ID: <1i7xi.249$LL7.189@nlpi069.nbdc.sbc.com>

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1187280032.791915_at_bubbleator.drizzle.com...

> Bob Jones wrote:
>> "Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message 
>> news:1187183849.417085.301070_at_b79g2000hse.googlegroups.com...

>>> On Aug 14, 11:26 pm, hjr.pyth..._at_gmail.com wrote:

>>>> On Aug 15, 9:37 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
>>>>
>>>>> Oracle 10g 10.2.0.3, Windows 2003 Server
>>>>> This may seem an easy question but I can't seem to find an answer.
>>>>> If an Oracle instance is set up for automatic memory management and 
>>>>> there
>>>>> are some parameters, such as bitmap_merge_area_size (etc) which are 
>>>>> non-zero
>>>>> and which Oracle recommends using the PGA_AGGREGATE_TARGET instead 
>>>>> ,should
>>>>> such parameters be zeroed out in the SPFILE?
>>>>> I know the manual says they aren't recommended except in Shared Server
>>>>> environments but what should be done in a Dedicated Server 
>>>>> environment?
>>>>> Thanks.
>>>> The old _AREA_SIZE  parameters are silently ignored if the new
>>>> parameter is present.
>>>>
>>>> You may want to leave them in the spfile at their non-zero values,
>>>> however, in case you switch to shared server mode (or, remembering
>>>> that an instance can do both shared- and dedicated-server mode at the
>>>> same time, in case one or two of your users need to connect in shared
>>>> mode).
>>>>
>>>> If you really want to get rid of the old parameters, don't just zero
>>>> them out, though. Delete them from the spfile entirely:
>>>>
>>>> alter system reset sort_area_size scope=spfile sid='*';
>>>>
>>>> ...that way, the parameter still has a non-zero default value for
>>>> those rare occasions when it might be needed.

>>> Helpful advice, I wasn't aware of how to remove a parameter from a
>>> SPFILE.

>>>

>>> But, the _AREA_SIZE parameters are not silently ignored. In my
>>> testing, the _AREA_SIZE parameters set the minimum values for those
>>> memory areas, and Oracle is able to adjust the value of the parameter
>>> upward as needed. If I recall correctly, SORT_AREA_SIZE may be
>>> automatically adjusted up to 5% of the PGA_AGGREGATE_TARGET and
>>> HASH_AREA_SIZE up to 10% of the PGA_AGGREGATE_TARGET. Prior to
>>> implementing Oracle 10.2.0.2, I found one query that was sorting to
>>> the temp tablespace under Oracle 10.2.0.2 (PGA_AGGREGATE_TARGET set at
>>> 4000MB, 5% = 200MB) that was not sorting to the temp tablespace under
>>> Oracle 8.1.7.3 with a SORT_AREA_SIZE of 10MB. Oracle 10.2.0.2 was
>>> apparently adjusting the SORT_AREA_SIZE to be just large enough for
>>> the session to perform a one pass sort to disk. I bumped the
>>> SORT_AREA_SIZE to 20MB, resulting in the elimination of the sort to
>>> the temp tablespace.

>>>

>>> Metalink Notes:223299.1, 223730.1 both agree with you that the
>>> _AREA_SIZE parameters ARE silently ignored. But, those articles are
>>> apparently wrong. A write-up by Burleson quoted contents of those
>>> Metalink articles, which lead to the development of this article that
>>> provides a test case that shows that the parameters are not ignored:
>>> http://www.jlcomp.demon.co.uk/untested.html

>>>
>>
>> Wow, this guy really has too much time in his hands, or he just hate this 
>> Don guy too much. It is pointless to set _AREA_SIZE when using 
>> PGA_AGGREGATE_TARGET anyway.
>
> You really need to drop the attitude or learn about the "rule of holes:"
> When you are in one stop digging.
>

What hole? Please be specific.

> Howard is 100% correct in everything he wrote. And you will find the exact 
> same criticism from Tom Kyte, and Jonathan Lewis, and many others
> who are the experts in our community.
>

Your responses aren't always to the point, but please for one time, provide some facts, not names.

> In case you are not familiar with Howard ... you might want to review
> this: 
> http://apex.oracle.com/pls/otn/f?p=19297:4:4116369228746107::NO:4:P4_ID:59
> --

Hmmm, more names and links? You must be a world class promoter. Received on Thu Aug 16 2007 - 20:59:24 CDT