--In 10g database, we want to use PGA_AGGEGATE_TARGET, however below excepted from Oracle Document "Database Reference" confuses me, as we have shared server running with this database. Do I still need to set it explicitly?
"Note: Oracle does not recommend using the
SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_SIZE is retained for backward compatibility."Conclusion from AskTom:
can safely leave sort_area_retained_size & sort_area_size as default shown below for 10g database using shared server, if set
PGA_AGGREGATE_TARGET and workarea_size_policy='auto' sort_area_retained_size integer 0sort_area_size integer 65536
ANSWERS from AskTom:
if ( version = 9i )
then
if ( pga_aggregate_target is set and workarea_size_policy is auto )
then
if ( connection is dedicated server )
then
these parameters (sort area [retained] size) need not be set
else
they do need to be set, shared server uses sort area size
end if
else
they do need to be set
end if
elsif (version > 9i )
then
if ( pga_aggregate_target is set and workarea_size_policy is auto )
then
these parameters (sort area [retained] size) need not be set
else
they do need to be set
end if
version 9i:
shared server -> sort_area_size is used regardless.
dedicated server AND workarea = auto -> pga_aggregate_target is used
dedicated server AND workarea = manual -> sort_area_size is used
version 10g
workarea=auto -> pga_aggregate_target
workarea= manual -> sort_area_size
you need not remove the sort_area_size, and since sessions could set workarea to manual - you might
just want to leave it in with YOUR default.
end if
Automatic PGA Memory Management
Starting with Oracle9i, Release 1, a new way to manage PGA memory was introduced that avoids using
the SORT_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and HASH_AREA_SIZE Parameters. It was introduced to
attempt to address a few issues:
* Ease of use: Much confusion surrounded how to set the proper *_AREA_SIZE parameters. There
was also much confusion over how those parameters actually worked and how memory was allocated.
* Manual allocation was a “one-size-fits-all” method: Typically as the number of users
running similar applications against a database went up, the amount of memory used for
sorting/hashing went up linearly as well. If 10 concurrent users with a sort area size of 1MB used
10MB of memory, 100 concurrent users would probably use 100MB, 1,000 would probably use 1000MB, and
so on. Unless the DBA was sitting at the console continually adjusting the sort/hash area size
settings, everyone would pretty much use the same values all day long. Consider the previous
example, where you saw for yourself how the physical I/O to temp decreased as the amount of RAM we
allowed ourselves to use went up. If you run that example for yourself, you will almost certainly
see a decrease in response time as the amount of RAM available for sorting increases. Manual
allocation fixes the amount of memory to be used for sorting at a more or less constant number,
regardless of how much memory is actually available. Automatic memory management allows us to use
the memory when it is available; it dynamically adjusts the amount of memory we use based on the
workload.
* Memory control: As a result of the previous point, it was hard, if not impossible, to keep
the Oracle instance inside a “box” memory-wise. You could not control the amount of memory the
instance was going to use, as you had no real control over the number of simultaneous sorts/hashes
taking place. It was far too easy to use more real memory (actual physical free memory) than was
available on the machine.
Enter automatic PGA memory management. Here, you first simply set up and size the SGA. The SGA is a
fixed-size piece of memory, so you can very accurately see how big it is, and that will be its
total size (until and if you change that). You then tell Oracle, “This is how much memory you
should try to limit yourself across all workareas—a new umbrella term for the sorting and hashing
areas you use.” Now, you could in theory take a machine with 2GB of physical memory and allocate
768MB of memory to the SGA and 768MB of memory to the PGA, leaving 512MB of memory for the OS and
other processes. I say “in theory” because it doesn’t work exactly that cleanly, but it’s close.
Before I discuss why that is true, we’ll take a look at how to set up automatic PGA memory
management and turn it on.
The process to set this up involves deciding on the proper values for two instance initialization
parameters, namely
* WORKAREA_SIZE_POLICY: This parameter may be set to either MANUAL, which will use the sort
area and hash area size parameters to control the amount of memory allocated, or AUTO, in which
case the amount of memory allocated will vary based on the current workload present in the
database. The default and recommended value is AUTO.
* PGA_AGGREGATE_TARGET: This parameter controls how much memory the instance should
allocate, in total, for all workareas used to sort/hash data. Its default value varies by version
and may be set by various tools such as the DBCA. In general, if you are using automatic PGA memory
management, you should explicitly set this parameter.
So, assuming that WORKAREA_SIZE_POLICY is set to AUTO, and PGA_AGGREGATE_TARGET has a nonzero
value, you will be using the new automatic PGA memory management. You can “turn it on" in your
session via the ALTER SESSION command or at the system level via the ALTER SESSION command.
Note Bear in mind the previously discussed caveat that in Oracle9i, shared server connections
will not use automatic memory management; rather, they will use the SORT_AREA_SIZE and
HASH_AREA_SIZE parameters to decide how much RAM to allocate for various operations. In Oracle 10g
and up, automatic PGA memory management is available to both connection types. It is important to
properly set the SORT_AREA_SIZE and HASH_AREA_SIZE parameters when using shared server connections
with Oracle9i.
So, the entire goal of automatic PGA memory management is to maximize the use of RAM while at the
same time not using more RAM than you want. Under manual memory management, this was virtually
impossible goal to achieve. If you set SORT_AREA_SIZE to 10MB, when one user was performing a sort
operation that user would use up to 10MB for the sort workarea. If 100 users were doing the same,
they would use up to 1,000MB of memory. If you had 500MB of free memory, the single user performing
a sort by himself could have used much more memory, and the 100 users should have used much less.
That is what automatic PGA memory management was designed to do. Under a light workload, memory
usage could be maximized as the load increases on the system, and as more users perform sort or
hash operations, the amount of memory allocated to them would decrease—to obtain the goal of using
all available RAM, but not attempting to use more than physically exists.
