How to Read PGA Memory Advisory Section in AWR and Statspack Reports [ID 786554.1]

How to Read PGA Memory Advisory Section in AWR and Statspack Reports [ID 786554.1]
		Modified 25-AUG-2010     Type HOWTO     Status PUBLISHED

In this Document
  Goal
  Solution
  References

---

 

 

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 10.2.0.4 - Release: 9.0.1 to 10.2
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 9.0.1.0 to 10.2.0.4

Goal

Goal of this note is to illustrate how to read  PGA Memory Advisory section in AWR and Statspack reports.

Solution

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 for DB: AAA  Instance: aaa  End Snap: 20555 



PGA Target    Size           W/A MB   W/A MB Read/      Cache  Overalloc
  Est (MB)   Factr        Processed Written to Disk     Hit %      Count
---------- ------- ---------------- ---------------- -------- ----------
        16     0.1     13,406,708.5      1,150,524.0     92.0     98,500
        32     0.3     13,406,708.5      1,149,545.5     92.0     98,500
        64     0.5     13,406,708.5      1,149,545.5     92.0     98,500
        96     0.8     13,406,708.5      1,149,545.5     92.0     98,500
       128     1.0     13,406,708.5        370,864.9     97.0     98,343
       154     1.2     13,406,708.5        358,442.9     97.0     73,884
       179     1.4     13,406,708.5        345,671.0     97.0     51,419
       205     1.6     13,406,708.5        325,909.7     98.0     34,441
       230     1.8     13,406,708.5        208,594.9     98.0      8,993
       256     2.0     13,406,708.5        158,403.9     99.0      4,272
       384     3.0     13,406,708.5        105,314.7     99.0        826
       512     4.0     13,406,708.5         99,935.0     99.0        176
       768     6.0     13,406,708.5         98,714.6     99.0         22
     1,024     8.0     13,406,708.5         98,433.7     99.0          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 128 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 99,935.0 in the example AWR report.
In other words, further increases of pga_aggregate_target won't give any more benefit. Column 'Size Factr' = 4.0 shows that current PGA size should be increased by 4 times (to 512MB) to reach this goal. 

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 the given example this goal is achieved with pga_aggregate_target = 1,024MB.

In many cases  'Estd PGA Overalloc Count' figures reach 0 before  the number in 'Estd Extra W/A MB Read/ Written to Disk ' stabilizes, as in the following example:

PGA Memory Advisory for DB: BBB  Instance: bbb  End Snap: 15315 


                                       Estd Extra    Estd PGA   Estd PGA
PGA Target    Size           W/A MB   W/A MB Read/      Cache  Overalloc
  Est (MB)   Factr        Processed Written to Disk     Hit %      Count
---------- ------- ---------------- ---------------- -------- ----------
       179     0.1      2,741,061.8      1,671,995.0     62.0     42,214
       359     0.3      2,741,061.8      1,625,275.4     63.0     39,903
       717     0.5      2,741,061.8      1,148,570.8     70.0     22,967
     1,076     0.8      2,741,061.8        455,187.2     86.0      2,433
     1,434     1.0      2,741,061.8        302,362.3     90.0          2
     1,721     1.2      2,741,061.8        294,467.8     90.0          0
     2,008     1.4      2,741,061.8        273,153.5     91.0          0
     2,294     1.6      2,741,061.8        273,075.2     91.0          0
     2,581     1.8      2,741,061.8        272,980.1     91.0          0
     2,868     2.0      2,741,061.8        272,980.1     91.0          0
     4,302     3.0      2,741,061.8        272,980.1     91.0          0
     5,736     4.0      2,741,061.8        272,980.1     91.0          0
     8,604     6.0      2,741,061.8        272,980.1     91.0          0
    11,472     8.0      2,741,061.8        272,980.1     91.0          0
------------------------------------------------------------------------

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.

References

NOTE:223730.1 - Automatic PGA Memory Management