Copyright (c) 2025, Oracle. All rights reserved. Oracle Confidential.
Sizing the PGA in Oracle 19c - How to Account for the MGA Size (Doc ID To
2808761.1) Bottom
In this Document
Goal
Solution
References
Applies to:
Oracle Database - Enterprise Edition - Version 18.0.0.0 and later
Oracle Database - Standard Edition - Version 18.1.0.0.0 and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Cloud Infrastructure - Exadata Cloud Service - Version N/A and later
Generation 1 - Exadata Cloud at Customer (First Generation Cloud Machine) - Version N/A and later
Information in this document applies to any platform.
Goal
In Oracle 18c and up, the space allocated to the MGA (Managed Global Area) is included in total
amount of memory allocated to the PGA. This can be see from v$pgastat:
SQL> select name, value from v$pgastat where name like 'MGA%';
NAME VALUE
---------------------------------------- ----------
MGA allocated (under PGA) 6442450944
Therefore it follows that when upgrading from an earlier version, the maximum allowable PGA size
(PGA_AGGREGATE_LIMIT) must be increased to account for the MGA. See the following note for a
detailed description of the MGA and its functions:
MGA (Managed Global Area) Reference Note (Document 2638904.1)
But how much should PGA_AGGREGATE_LIMIT be increased to account for the MGA?
Solution
A rule of thumb is as follows:
PGA_AGGREGATE_LIMIT =(original PGA_AGGREGATE_LIMIT value) + ((maximum number of connected
processes) * 5M)
The "original PGA_AGGREGATE_LIMIT value" is the PGA_AGGREGATE_LIMIT prior to upgrading to 18c
or 19c, and "maximum number of connected processes" is the upper limit of processes that could be
connected to the database at any one time (ostensibly equal to the value of the PROCESSES
parameter). This also could be determined for any given instance lifetime as follows:
select max_utilization from v$resource_limit where resource_name='processes';
However, there is a more 'scientific' method of determining this value based on values currently
retrievable in the database. The generic equation is:
((maximum aggregate PGA in use for the life of the instance) + ((maximum number of attached
processes for the life the instance) * 5M)) * 1.1
The following query can be used to determine the new recommended PGA aggregate limit:
WITH
MAX_PGA as
(select round(value/1024/1024,1) max_pga from v$pgastat where name='maximum PGA
allocated'),
MGA_CURR as
(select round(value/1024/1024,1) mga_curr from v$pgastat where name='MGA allocated (under
PGA)'),
MAX_UTIL as
(select max_utilization as max_util from v$resource_limit where resource_name='processes')
SELECT
a.max_pga "Max PGA (MB)",
b.mga_curr "Current MGA (MB)",
c.max_util "Max # of processes",
round(((a.max_pga - b.mga_curr) + (c.max_util * 5)) * 1.1, 1) "New PGA_AGGREGATE_LIMIT (MB)"
FROM MAX_PGA a, MGA_CURR b, MAX_UTIL c
WHERE 1 = 1;
The following gives an example:
SQL> WITH
2 MAX_PGA as
3 (select round(value/1024/1024,1) max_pga from v$pgastat where name='maximum PGA allocated'),
4 MGA_CURR as
5 (select round(value/1024/1024,1) mga_curr from v$pgastat where name='MGA allocated (under
PGA)'),
6 MAX_UTIL as
7 (select max_utilization as max_util from v$resource_limit where resource_name='processes')
8 SELECT
9 a.max_pga "Max PGA (MB)",
10 b.mga_curr "Current MGA (MB)",
11 c.max_util "Max # of processes",
12 round(((a.max_pga - b.mga_curr) + (c.max_util * 5)) * 1.1, 1) "New PGA_AGGREGATE_LIMIT (MB)"
13 FROM MAX_PGA a, MGA_CURR b, MAX_UTIL c
14 WHERE 1 = 1;
Max PGA (MB) Current MGA (MB) Max # of processes New PGA_AGGREGATE_LIMIT (MB)
------------ ---------------- ------------------ ----------------------------
1231.5 343.1 466 3451.4
To reduce PGA usage and avoid hitting the PGA_AGGREGATE_LIMIT, you can reduce the value of the
PROCESSES parameter based on your system requirement.
