Re: "Depreciated" Parameters In SPFILE

On Aug 15, 11:17 pm, 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.

Charles, I may be getting confused as to the point you're making.

Jonathan's article says 'So the sort_area_size does have some relevance - even when all the new pga mechanisms are correctly enabled... ****IF***** ...you (like me) are running with shared servers (formerly multi-threaded servers) and know that for Oracle 9i, the older parameters are still used to limit the memory that gets allocated in the SGA (which is where the UGA is going to be held when using shared servers)."

Jonathan is using Shared Servers. In 9i and early 10g, shared servers absolutely relied on SORT_AREA_SIZE. That's why my earlier advice was to leave them in the spfile so that they "still ha[ve] a non-zero default value for those rare occasions when [they] might be needed" - namely, when you connect with shared servers to an otherwise mostlydedicated  server environment.

I have not seen anything in Jonathan's article that indicates that the old parameters are anything other than silently ignored **if** you are using dedicated server processes. I could be wrong: I find that article quite hard to read because of its incremental addenda.

Certainly, in 11g, if you set MEMORY_TARGET and *also* set PGA_AGGREGATE_TARGET, then the P_A_T is regarded as a minimum memory demand that must be satisfied from the memory_target. So it's possible that sort of behaviour did take place in 10g, despite all the documentation saying otherwise. I haven't tested it rigorously myself, I will admit.

Regards
HJR Received on Thu Aug 16 2007 - 21:28:09 CDT