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 03:13:14 GMT
Message-ID: <ehPwi.2086$>

"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 PGA_AGGREGATE_TARGET anyway. Received on Wed Aug 15 2007 - 22:13:14 CDT

Original text of this message