Re: "Depreciated" Parameters In SPFILE

On Aug 16, 10:35 pm, hjr.pyth..._at_gmail.com wrote:
> 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:
>
> > > > 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
Howard,

Thanks for the response.

I re-read the article that I referenced (for a third time) and now see that it does _not_ state that the _AREA_SIZE parameters have an effect - thanks for bringing this to my attention.

I am working on a test case to demonstrate the behavior that I noticed under the base patch of Oracle 10.2.0.2, where changing the value of the SORT_AREA_SIZE did affect the number of sorts to disk, as Oracle switched from a one pass sort to an optimal sort. Looking over my notes from the read through of "Cost-Based Oracle Fundamentals", the book indicates that the switch from a one pass sort to an optimal sort can (or will likely) increase CPU usage - this may or may not affect the ability of reproducing the behavior as the increase in forecasted CPU usage affects a plan's cost. So far, my test case on Oracle 10.2.0.3 is agreeing with your statement of "The old _AREA_SIZE parameters are silently ignored if the new [PGA_AGGREGATE_TARGET] parameter is present." I may need to back off the position that the parameters still have an effect when PGA_AGGREGATE_TARGET is set. The testing will continue.

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