Activating, Monitoring, and Tuning Automatic PGA Memory Management via PGA_AGGREGATE_TARGET Under Oracle 9i - Page 2
March 14, 2003Jim Czuprynski
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: