Activating, Monitoring, and Tuning Automatic PGA Memory Management via PGA_AGGREGATE_TARGET Under Oracle 9i - Page 2

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.

• The Oracle DBMS dynamically derives the value for aggregate PGA auto target from the value set for PGA_AGGREGATE_TARGET and is continuously adjusted by Oracle. It is the amount of memory that can be used for work areas running in automatic mode. If this value is small, it generally indicates that other components of the system--for example, PL/SQL or Java memory--are using a lot of PGA, leaving little behind for work areas to be managed in automatic mode.

• global memory bound shows the maximum size of a work area executed in automatic mode. The value is constantly adjusted by Oracle based on the current state of the work area workload and generally decreases when the number of active work areas increase. Oracle recommends that this value should never reach 1MB; if it does, it's probably an indicator that PGA_AGGREGATE_TARGET should be increased.

• total PGA allocated yields the total amount of PGA memory that Oracle has allocated for the instance, while total PGA used for auto workareas tells how much memory is in use by other processes like PL/SQL or Java. Subtracting the second number from the first yields the total PGA memory used by these other processes.

• over allocation count tells how much PGA memory has been over-allocated cumulatively since the instance was started. If the value returned is anything over zero, it is an indication that the size of PGA_AGGREGATE_TARGET should be increased because it means that Oracle could not honor at least one request for additional PGA work areas.

• total bytes processed represents how many bytes were processed since instance startup, while extra bytes read/written represents how many bytes were processed via one-pass or multi-pass processing. These two values are used to calculate the cache hit percentage based on the following formula: (100 * total bytes processed) / (total bytes processed + extra bytes read/written).