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: Charles Hooper <>
Date: Wed, 15 Aug 2007 06:17:29 -0700
Message-ID: <>

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 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, 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:

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Aug 15 2007 - 08:17:29 CDT

Original text of this message