Oracle 12c – Resolving PGA_AGGREGATE_LIMIT Warnings in Alert Log

configurationoracleoracle-12c

Introduction

During the startup process of our Oracle instances, we are encountering the following error message in the alert log:

WARNING: pga_aggregate_limit value is too high for the
amount of physical memory on the system
  PGA_AGGREGATE_LIMIT is 2048 MB
  PGA_AGGREGATE_TARGET is 256 MB.
  physical memory size is 0 MB
  limit based on physical memory and SGA usage is 512 MB
  SGA_TARGET is 6144 MB
Using default pga_aggregate_limit of 2048 MB

Physical RAM

What is strange is the fact that the physical memory size in the WARNING is being displayed as 0 MB, because our physical server has 768 GB of physical RAM. (Yes, the server isn't a virtual server; physical only for Oracle.)

Spfile and Pfile Configuration

The SGA_TARGET and SGA_MAX_TARGET parameters in the pfile_SID.ora and spfile_SID.ora have been configured at 6144M. Here a list of the interesting parameters from the PFILE:

...
processes              = 500
...
memory_max_target      = 0
memory_target          = 0
sga_max_size           = 6144M
sga_target             = 6144M
...
pga_aggregate_target   = 256M
...

Documentation for PGA_AGGREGATE_LIMIT

According to the Oracle documentation for PGA_AGGREGATE_LIMIT the value will be set according to the following rules:

By default, PGA_AGGREGATE_LIMIT is set to the greater of…

  • 2 GB,
  • 200% of PGA_AGGREGATE_TARGET,
  • and 3 MB times the PROCESSES parameter.

It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.

(Important bit at the end regarding restrictions)

Oracle Patch Level

The current patch level of the server is: 

Patch Id: 26758841
Patch Description: WINDOWS DB BUNDLE PATCH 12.2.0.1.171017(64bit):26758841

Applying Documentation to Oracle Configuration

Because we haven't manually configured the PGA_AGGREGATE_LIMIT parameter Oracle is going to be so nice and do that for us. This means Oracle is going to set the value to the greater of the following values:

  • 2 GB
  • 200% of PGA_AGGREGATE_TARGET = 200% * 256 MB = 512 MB
  • 3 MB times PROCESSES = 3 MB * 500 = 1.5 GB

…but there would be the restriction cited above, which in my case would mean:

90% * Physical Memory - 6144 MB = 90% * 0 MB - 6144 MB = <0 MB

Ok. Because Oracle is listing the server's physical RAM as 0 MB we are going to hit the restriction and Oracle should throttle the PGA_AGGREGATE_LIMIT value to 256 MB.

But Oracle is setting the value to 2048 MB!

Questions

  1. Why is the physical RAM being listed as 0 MB?
  2. Why is Oracle setting the PGA_AGGREGATE_LIMIT to 2048 MB instead of 256 MB?
  3. Can I safely ignore the warning?

Best Answer

According to our database admins, this is a known ORACLE error since version 12. We have been advised to set pga_aggregate_limit to 0 in order to work around this problem. I haven't been able to test this thoroughly, but we haven't seen errors since changing this setting (using alter system, we haven't set the parameter in our database's [s]pfile).

Related Question