How to Read PGA Memory Advisory Section in AWR and Statspack Reports

APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.0.1.0 and later Information in this document applies to any platform. ***Checked for relevance on 12-Nov-2013***

GOAL
The goal of this note is to illustrate how to read/interpret the PGA Memory Advisory section in AWR and Statspack reports, and tune the PGA_AGGREGATE_TARGET instance parameter based on the findings.

SOLUTION
The preferred and easiest way of monitoring and setting the PGA_AGGREGATE_TARGET instance parameter is by examining the 'PGA Memory Advisory' section in an AWR or Statspack report.
PGA Memory Advisory for DB: AAA Instance: aaa End Snap: 20555

Estd Extra Estd P Estd PGA

  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 need to find the row with the 'Size Factr' column value of 1.0. This column indicates the size factor of the PGA estimates; a value of 1 indicates the current PGA size. The 'PGA Target Est(MB)' value of this row will show your current PGA size: 128MB in this example. Other 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.0, you get estimates 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. A lower value means less work areas have to be spilled to disk, enhancing performance of the Oracle instance.

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. In the example output this happens at value 99,935.0. In other words, further increases of PGA_AGGREGATE_TARGET won't give any substantial benefit and will only waste memory. The row corresponding to this value shows a 'Size Factr' column vale of 4.0, indicating that the current PGA size should be increased 4 times (to 512MB) to reach this goal.

The 'Estd PGA Overalloc Count' column shows how many times the database instance processes would need to request more PGA memory at the OS level than the amount shown in the 'PGA Target Est (MB)' value of the respective row. Ideally this field should be 0 (indicating that the PGA is correctly sized, and no overallocations should take place), 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

------------------------------------------------------------------------

The question on whether to increase or decrease the PGA_AGGREGATE_TARGET from the current value should be always investigated. The answer depends on how much of total memory (SGA+PGA) can be allocated for this database instance on the machine, taking into account memory needs of other database instances on the same machine, non-Oracle software and the OS itself. Too much memory allocated wastes memory, and too less memory allocated causes possible performance issues in the Oracle environment.