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
 
 
Search WWH ::




Custom Search