Re: "Depreciated" Parameters In SPFILE

On Aug 15, 9:17 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Very fine reference article.

IMHO -- Mark D Powell -- Received on Wed Aug 15 2007 - 08:30:47 CDT