Re: "Depreciated" Parameters In SPFILE

On Aug 17, 12:28 pm, hjr.pyth..._at_gmail.com wrote:
> 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 mostly-
> dedicated 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
I meant to add that in that article, Jonathan **does** go on to use a dedicated server for testing... but he does so only to disprove an assertion made by the World's Greatest Oracle Database Expert that PGA_AGGREGATE_TARGET cannot be set above 200MB: "So let's see if there is any difference between running the same query (through a **dedicated server** this time) with a pga_aggregate_target of 200MB, as opposed to a pga_aggregate_target of 500 MB, or 1000 MB."

I don't see him saying that _AREA_SIZE parameters are having an effect on his results at that particular point.

Regards
HJR Received on Thu Aug 16 2007 - 21:35:02 CDT