In this section of the document we will learn how to read the pga memory advisory that shows in the AWR report.
In the above diagram look for your PGA setting “1.0” which will be your PGA value in the parameter file. You can verify by logging in to the database and query show parameter PGA
NAME TYPE VALUE
----- ------ -------
pga_aggregate_target big integer 1G
Other field that we should look at is
a) Estd Extra W/A MB Read/Written to Disk
b) Overall 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.
When reading “Estd Extra W/A MB Read/Written to Disk” our goal should be of setting a PGA to a value which does not substantially reduce any more.
See “4427.31” in the column and that shows PGA value to be 1229 MB and all values after that are similar which means increasing PGA to more than 1229 MB will not give any more benefit as the value remains the same.
Now second 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. This field should be “0” and in the above example this is acheived with a value of 2048 MB.
So to achieve the goal and enhance the performance from the above diagram PGA should be set to a value of 2G however 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.
In many cases ‘Estd PGA Overalloc Count’ figures reach 0 before the number in ‘Estd Extra W/A MB Read/ Written to Disk ‘ stabilizes and hence requires investigation whether increasing the memory of PGA is permissible on the database box taking into account memory needs.
Reference : How to Read PGA Memory Advisory Section in AWR and Statspack Reports [ID 786554.1]