A doc from Metalink:
Preferred and easiest way of monitoring and setting pga_aggregate_target parameter (PGA) is section ‘PGA Memory Advisory’ in the AWR and Statspack reports.
PGA Memory Advisory
- When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0
| PGA Target Est (MB) | Size Factr | W/A MB Processed | Estd Extra W/A MB Read/ Written to Disk | Estd PGA Cache Hit % | Estd PGA Overalloc Count |
|---|---|---|---|---|---|
| 128 | 0.13 | 258,572.74 | 127,776.77 | 67.00 | 2,010 |
| 256 | 0.25 | 258,572.74 | 31,433.28 | 89.00 | 0 |
| 512 | 0.50 | 258,572.74 | 29,560.27 | 90.00 | 0 |
| 768 | 0.75 | 258,572.74 | 28,863.55 | 90.00 | 0 |
| 1,024 | 1.00 | 258,572.74 | 28,863.55 | 90.00 | 0 |
| 1,229 | 1.20 | 258,572.74 | 4,643.11 | 98.00 | 0 |
| 1,434 | 1.40 | 258,572.74 | 4,643.11 | 98.00 | 0 |
| 1,638 | 1.60 | 258,572.74 | 4,643.11 | 98.00 | 0 |
| 1,843 | 1.80 | 258,572.74 | 4,643.11 | 98.00 | 0 |
| 2,048 | 2.00 | 258,572.74 | 4,643.11 | 98.00 | 0 |
| 3,072 | 3.00 | 258,572.74 | 4,643.11 | 98.00 | 0 |
| 4,096 | 4.00 | 258,572.74 | 4,643.11 | 98.00 | 0 |
| 6,144 | 6.00 | 258,572.74 | 4,643.11 | 98.00 | 0 |
| 8,192 | 8.00 | 258,572.74 | 4,643.11 | 98.00 | 0 |
In this section, you first find the row where field ‘Size Factr’ is 1.00. The field ‘PGA Target Est(MB)’ of this row will show your current PGA setting – figure 1024 in the above example. Other fields (columns) you will be interested in are: ‘Estd Extra W/A MB Read/ Written to Disk ‘ and ‘Estd PGA Overalloc Count’.
When you go down or up the advisory section from the row with ‘Size Factr’ = 1.00, you get estimations for Disk usage – column ‘Estd Extra W/A MB Read/ Written to Disk ‘ - for bigger or smaller settings of pga_aggregate_target. The less Disk usage figure in this column, usually the better.
Your first goal is to have such a setting of pga_aggregate_target, that number in the column ‘Estd Extra W/A MB Read/ Written to Disk ‘ does not substantially reduce any more, see figure 28863.55 in the example AWR report.
In other words, further increases of pga_aggregate_target won’t give any more benefit.
Column ‘Estd PGA Overalloc Count’ shows estimations of how many times database would need to request from OS more PGA memory than the amount shown in the ‘PGA Target Est(MB)’ field of the respective row. Ideally this field should be 0, and that is your equally important second goal.
In many cases ‘Estd PGA Overalloc Count’ figures reach 0 before the number in ‘Estd Extra W/A MB Read/ Written to Disk ‘ stabilizes
Question whether increase from the current actual size is possible for a given database, should be always investigated. The answer depends on how much of total memory (SGA+PGA) can be allocated for this database on this box, i.e. take into account memory needs of other databases, software and OS residing on the box.
Ref:DOC ID:786554.1
I am wondering the reason the specific Metalin document (786554.1) is applicable up to 10.2.0.4 and not 11g
Beginning with Oracle Database 11g, Oracle Database can manage the SGA memory and instance PGA memory completely automatically. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. With this memory management method, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.
To achieve this, two new parameters have been introduced named MEMORY_MAX_TARGET and MEMORY_TARGET. To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).