Re: "Depreciated" Parameters In SPFILE

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

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