How to Read PGA Memory Advisory Section in AWR and Statspack Reports (文档 ID 786554.1)

In this Document

Goal

Solution

---

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.

相关内容

产品

• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)

关键字

AWR; PERFORMANCE; PGA; PGA_AGGREGATE_TARGET; SIZING; STATSPACK