Daily Archives: 22.07.2019

WARNING: pga_aggregate_limit value is too high for the amount of physical memory on the system

This entry was posted in Oracle 12c on by

On Oracle 12.2 I have this fancy message after the database mounts. It is a datawarehouse and there are quite some memory.

The full message is:

2019-07-02T17:41:33.036963+02:00
 WARNING: pga_aggregate_limit value is too high for the
 amount of physical memory on the system
   PGA_AGGREGATE_LIMIT is 20480 MB
   PGA_AGGREGATE_TARGET is 10240 MB.
   physical memory size is 302063 MB
   limit based on physical memory and SGA usage is 512 MB
   SGA_TARGET is 276480 MB

There are 300GB of physical memory on the server.

SGA_TARGET is set to 270GB, PGA_AGGREGATE_LIMIT to 20GB. There should be 10GB available for the OS to run even when the PGA usage goes to the maximum it is defined.

So why does Oracle gives me this message?

The answer is in the documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/PGA_AGGREGATE_LIMIT.html#GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3

Even though PGA_AGGREGATE_LIMIT is set to a value different from 0, Oracle calculates so that it does not overpass 90% of the physical memory.

90% of 300GB is 270GB, minus 270GB from SGA size, means there is nothing left. Fortunately it is not in action, and I can see that by checking the maximum PGA used since instance start:

SQL> select round(value/1024/1024/1024) max_used from v$pgastat where name='maximum PGA allocated';
  MAX_USED
----------
        16