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% ofPGA_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
- Why is the physical RAM being listed as
0 MB
? - Why is Oracle setting the
PGA_AGGREGATE_LIMIT
to2048 MB
instead of256 MB
? - Can I safely ignore the warning?