Re: "Depreciated" Parameters In SPFILE

On Aug 16, 10:35 pm, hjr.pyth...@xxxxxxxxx wrote:
On Aug 17, 12:28 pm, hjr.pyth...@xxxxxxxxx wrote:
On Aug 15, 11:17 pm, Charles Hooper <hooperc2...@xxxxxxxxx> wrote:

On Aug 14, 11:26 pm, hjr.pyth...@xxxxxxxxx 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.

.

Relevant Pages

  • Oracle 9i AS
    ... I'm looking for any people on the list with experience of Oracle 9i ... Application server on TRU64. ... I am trying to establish what sort of spec ...
    (Tru64-UNIX-Managers)
  • Re: Questions about shared server
    ... In our application we used dedicated server in Oracle 8.1.7, ... A tuning expert recommended NOT using multi-threaded server was and it ... Shared Server Processes". ... A shared server process can be more efficient ...
    (comp.databases.oracle.server)
  • Questions about shared server
    ... A tuning expert recommended NOT using multi-threaded server was and it ... Having upgraded to Oracle 9.2.0.4 for Linux there are some performance ... Shared Server Processes". ... A shared server process can be more efficient ...
    (comp.databases.oracle.server)
  • Re: Another oracle resource goes down the drain
    ... "In the Oracle documentation, it indicates that the only parameter needed ... instantiate 1 dispatcher by default to handle shared server traffic. ...
    (comp.databases.oracle.server)
  • Re: sor area size
    ... > I am using Oracle 9.2.0.4 on Win 2003 server. ... If you can, use automatic PGA management. ... and only need to set them if you need shared server or a specific switch ...
    (comp.databases.oracle.server)