Database Reference
In-Depth Information
•
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 couldn't control the amount of memory
the instance was going to use, as you had no real control over the number of simultaneous
sorts and hashes taking place. It was far too easy to attempt 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 (unless and until you
change it). You then tell Oracle, “This is how much memory you should try to limit yourself to across all
work areas
”
(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's true, let's take a look at how to set up automatic PGA memory management and turn it on.
The process of setting this up involves deciding on the proper values for two instance initialization parameters:
•
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 currently allocated PGA
memory to the instance. The default and recommended value is
AUTO
.
PGA_AGGREGATE_TARGET
: This parameter controls how much memory the instance should
allocate, in total, for all work areas used to sort or 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.
•
■
In Oracle 11
g
release 1 and above, instead of setting the
PGA_AGGREGATE_TARGET
, you can set the
MEMORY_TARGET
parameter. When the instance uses the
MEMORY_TARGET
parameter, it decides how much memory to
allocate to the SGa and pGa respectively. It may also decide to reallocate these memory amounts while the database
is up and running. This fact, however, doesn't affect how automatic pGa memory management (described later in this
chapter) works; rather it just decides the setting for the
PGA_AGGREGATE_TARGET
.
Note
So, assuming that
WORKAREA_SIZE_POLICY
is set to
AUTO
(the default) and
PGA_AGGREGATE_TARGET
has a nonzero
value, you will be using 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 SYSTEM
command.
■
Bear in mind the previous caveat that in Oracle9
i
, 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 10
g
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 Oracle9
i
.
Note