Oracle 9i Automatic PGA Memory Management
With Oracle 9i a new method of tuning the PGA memory areas was introduced. Automatic PGA Memory Management may be used in place of setting the sort_area_size, sort_area_retained_size, sort_area_hash_size and other related memory management parameters that all Oracle DBA's are familiar with. Those parameters may however still be used. See the following for an interesting discussion on this topic:

The Snark Research Mechanism

The PGA memory management may now controlled by just two parameters if that's how you choose to set it up.

Note that work_area_size_policy can be altered per database session, allowing manual memory management on a per session basis if needed. eg. a session is loading a large import file and a rather large sort_area_size is needed. A logon trigger could be used to set the work_area_size policy for the account doing the import.

A session is normally allowed to use up to approximately 5% of the PGA memory available. This is controlled by the undocumented initialization parameter _smm_max_size. This value is specified in kilobytes. eg. a value of 1000 really means 1000k. As with all undocumented parameters, don't expect help from Oracle support with it, as you are not supposed to use it. If you experiment with it, do so on a test system.

Also note that Automate PGA management can only be used for dedicated server sessions.

For more some good reading on Automatic PGA management, please see:

Oracle Documentation for Tuning PGA

The documentation contains some good guidelines for initial settings, and how to monitor and tune them as needed.

If your 9i database is currently using manual PGA management, there are views available to help you make a reasonable estimate for the setting.

If your database also has statspack statistics, then there is also historical information available to help you determine the setting.

An initial setting can be determined by simply monitoring the amount of PGA memory being used by the system as seen in v$pgastat, and by querying the v$pga_target_for_estimate view.

v$pgastat:


select *
from v$pgastat
order by lower(name)
/

NAME                                                  VALUE UNIT
---------------------------------------- ------------------ ------------
aggregate PGA auto target                      8,294,400.00 bytes
aggregate PGA target parameter                25,165,824.00 bytes
bytes processed                               24,929,280.00 bytes
cache hit percentage                                  86.31 percent
extra bytes read/written                       3,953,664.00 bytes
global memory bound                            1,257,472.00 bytes
maximum PGA allocated                         26,661,888.00 bytes
maximum PGA used for auto workareas              172,032.00 bytes
maximum PGA used for manual workareas            525,312.00 bytes
over allocation count                                   .00
PGA memory freed back to OS                    6,750,208.00 bytes
total freeable PGA memory                         65,536.00 bytes
total PGA allocated                           23,957,504.00 bytes
total PGA inuse                               15,283,200.00 bytes
total PGA used for auto workareas                       .00 bytes
total PGA used for manual workareas                     .00 bytes

16 rows selected.
The statistic "maximum PGA allocated" will display the maximum amount of PGA memory allocated during the life of the instance.

The statistic "maximum PGA used for auto workareas" and "maximum PGA used for manual workareas" will display the maximum amount of PGA memory used for each type of workarea during the life of the instance.

v$pga_target_advice:


select *
from v$pga_target_advice
order by pga_target_for_estimate
/


      PGA TARGET PGA TARGET                       ESTIMATED EXTRA ESTIMATED PGA ESTIMATED OVER
         FOR EST     FACTOR ADV  BYTES PROCESSED         BYTES RW   CACHE HIT %    ALLOC COUNT
---------------- ---------- --- ---------------- ---------------- ------------- --------------
      12,582,912        .50 ON        17,250,304                0        100.00              3
      18,874,368        .75 ON        17,250,304                0        100.00              3
      25,165,824       1.00 ON        17,250,304                0        100.00              0
      30,198,784       1.20 ON        17,250,304                0        100.00              0
      35,231,744       1.40 ON        17,250,304                0        100.00              0
      40,264,704       1.60 ON        17,250,304                0        100.00              0
      45,297,664       1.80 ON        17,250,304                0        100.00              0
      50,331,648       2.00 ON        17,250,304                0        100.00              0
      75,497,472       3.00 ON        17,250,304                0        100.00              0
     100,663,296       4.00 ON        17,250,304                0        100.00              0
     150,994,944       6.00 ON        17,250,304                0        100.00              0
     201,326,592       8.00 ON        17,250,304                0        100.00              0

12 rows selected.
Querying v$pga_target_advice can help you determine a good setting for pga_aggregate_target. As seen in the previous query, an 18M PGA setting would have caused Oracle to allocate more memory than specified on 3 occasions. With a 25M PGA, this would not have happened.

Keep in mind that pga_aggregate_target is not set in stone. It is used to help Oracle better manage PGA memory, but Oracle will exceed this setting if necessary.

There are other views that are also useful for PGA memory management.

v$process:

select
   max(pga_used_mem) max_pga_used_mem
   , max(pga_alloc_mem) max_pga_alloc_mem
   , max(pga_max_mem) max_pga_max_mem
from v$process
/
This will show the maximum PGA usage per process: 


select
   max(pga_used_mem) max_pga_used_mem
   , max(pga_alloc_mem) max_pga_alloc_mem
   , max(pga_max_mem) max_pga_max_mem
from v$process
/
This displays the sum of all current PGA usage per process: 


select
   sum(pga_used_mem) sum_pga_used_mem
   , sum(pga_alloc_mem) sum_pga_alloc_mem
   , sum(pga_max_mem) sum_pga_max_mem
from v$process
/
Be sure to read the documentation referenced earlier, it contains an excellent explanation of Automatic PGA Memory Management.

Following are some already canned scripts that may be of use.

PGA Monitoring Scripts