2

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?
0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.