Using PGA_AGGREGATE_TARGET To Activate Automatic Memory Management
To activate automatic PGA memory management, the PGA_AGGREGATE_TARGET
initialization parameter must be set to a non-zero value (Oracle defaults its
setting to zero unless overridden). However, just as we would do with tuning
the SGA, Oracle recommends setting an initial value, evaluating the results
over time, and then tuning the PGA based on those results.
The
recommended initial setting for PGA_AGGREGATE_TARGET depends on whether the
database is primarily being used for OLTP processing, which use relatively
small amounts of PGA memory, or for decision support systems (DSS), which
typically handle much larger, long-running, memory intensive queries. In either
case, Oracle suggests using no more than 80% of the total memory available for
the database instance to allow enough memory for other non-Oracle applications
running on the server.
In my case,
my server had 8GB available memory for the instance. For an OLTP-based server,
Oracle recommends allocating 20% to the PGA, so PGA_AGGREGATE_TARGET would be
set to approximately 1310MB ((8192 MB x 80%) x 20%). For a DSS-based server,
Oracle recommends a factor of at least 50%, or approximately 3276MB (8192MB x 80%)
x 50%). During my testing, I hedged my bet by quite a bit, knowing that my
database server is almost totally dedicated to OLTP, and initially allocated
400MB for PGA_AGGREGATE_TARGET.
After
applying the change to my database's INIT.ORA file and restarting the database,
I confirmed the results by querying V$PGASTAT. This is a new dynamic
view available with Oracle 9i and is useful for obtaining instance-level
statistics about PGA memory usage and how well automatic memory management is
working:
SQL> SELECT NAME, VALUE FROM v$pgastat;
NAME VALUE
------------------------------------- ----------
aggregate PGA target parameter 419430400
aggregate PGA auto target 361433088
global memory bound 20971520
total PGA inuse 17833984
total PGA allocated 34810880
maximum PGA allocated 124318720
total freeable PGA memory 0
PGA memory freed back to OS 0
total PGA used for auto workareas 0
maximum PGA used for auto workareas 60204032
total PGA used for manual workareas 0
maximum PGA used for manual workareas 246784
over allocation count 0
total bytes processed 5866293248
extra bytes read/written 720095232
cache hit percentage 89.06
The
statistics returned explain what's going on inside the PGA. Here is a breakout
of the more important ones, according to Oracle:
-
aggregate PGA
target parameter shows
the actual value set for PGA_AGGREGATE_TARGET (in this case, 400MB). This
parameter confirms if automatic PGA memory management has been activated--if
it hasn't been, then this value will be zero.
