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

2 Answers 2

1

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

0

According to a blog post by Mike Dietrich:

In Oracle Database 12.2.0.1 the default value gets adjusted a bit as it turned out that restricting PGA_AGGREGATE_LIMIT to a value too low will lead to a significant number of issues.

See the Oracle Database 12.2.0.1 documentation on PGA_AGGREGATE_LIMIT explaining:

If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.

If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.

If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.

In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter.

And please remember, DO NOT use MEMORY_TARGET as it will lead to issues such as no use of huge pages etc.

So the solution is to manually set the PGA_AGGREGATE_LIMIT parameter to a value twice the size of PGA_AGGREGATE_TARGET. Because in our case this value is 500 (processes) x 3 MB = 1.5 GB, we had to set the value of PGA_AGGREGATE_LIMIT to 3 GB.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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