Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: "Depreciated" Parameters In SPFILE

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$>

"DA Morgan" <> wrote in message

> Bob Jones wrote:
>> "Charles Hooper" <> wrote in message 

>>> On Aug 14, 11:26 pm, wrote:
>>>> On Aug 15, 9:37 am, "Dereck L. Dietz" <> wrote:
>>>>> Oracle 10g, 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

>>> 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, I found one query that was sorting to
>>> the temp tablespace under Oracle (PGA_AGGREGATE_TARGET set at
>>> 4000MB, 5% = 200MB) that was not sorting to the temp tablespace under
>>> Oracle with a SORT_AREA_SIZE of 10MB. Oracle 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:
>> 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 
> 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: 
> -- 

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

Original text of this message