ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

=====================

Error in oracle startup

=====================

SQL> Connected to an idle instance.

SQL> ORA-01078: failure in processing system parameters

ORA-00093: pga_aggregate_limit must be between 16384M and 100000G

SQL> Disconnected

=====================

What to check

=====================

COL name for A30
COL VALUE for A30
SELECT name, value 

  FROM V$PARAMETER 

 WHERE name = 'pga_aggregate_limit';

NAME                           VALUE
------------------------------ ------------------------------
pga_aggregate_limit            3145728000

3145728000=3000M

=====================

Solution

=====================

SQL> ALTER SYSTEM SET pga_aggregate_limit=0 SCOPE=BOTH;
System altered.

Issue resolved

=====================

Theory

=====================

As per Oracle documentation

In Oracle Database 12c, a new parameter called PGA_AGGREGATE_LIMIT sets a hard limit on the amount of PGA taken up by an instance. 

When the overall PGA occupied by all the sessions in that instance exceed the limit, Oracle kills the session holding the most untunable PGA memory, releasing all PGA memory held by that session.

Oracle PGA Memory Management

Theory

By default, Oracle Database automatically manages the total amount of memory dedicated to the instance PGA. 

You can control this amount by setting the initialization parameter PGA_AGGREGATE_TARGET.

This sets a logical ceiling to Oracle for all database server processes and background processes , but this ceiling can be exceeded.

What if the value is not set

If PGA_AGGREGATE_LIMIT is not set, then Oracle Database determines an appropriate default limit.

If PGA_AGGREGATE_TARGET is not set, the database chooses a default value for it.

Type of Control:

PGA_AGGREGATE_TARGET (Target): Acts as a target, not a strict limit. Oracle uses this to estimate the ideal memory for SQL operations.

PGA_AGGREGATE_LIMIT (Limit): Sets an absolute, hard maximum on the aggregate PGA memory that can be allocated.

Behavior When Exceeded:

Target: PGA can exceed the target, potentially causing swapping if it grows too high.

Limit: If this limit is hit, Oracle immediately terminates sessions consuming the most "untunable" PGA memory.

Calculate Values:

Target: Usually set manually by the DBA based on system memory.

Limit: Defaults to the greater of either 2GB, 200% of PGA_AGGREGATE_TARGET, or 3MB per process (up to 120% of physical memory minus SGA).

How to Calculate PGA_AGGREGATE_TARGET:

PGA_AGGREGATE_TARGET

is calculated based on available physical memory.

Take 80% of total OS memory(reserving 20% for the OS)

For OLTP systems it is typically set to 20% of the taken memory.      (Total Physical Memory * 0.8) * 0.2

For DSS/Data Warehouse systems it is set to 50% of the taken memory.  (Total Physical Memory * 0.8) * 0.5

PGA_AGGREGATE_LIMIT

SELECT limit_value*3 as 3x_proc_limit_mb 

  FROM V$RESOURCE_LIMIT 

 WHERE resource_name = 'processes';

Take the higher of these 3 values:

A. 2*PGA_AGGREGATE_TARGET,
B. 3x_proc_limit_mb (above)

C. 2Gb

But... do not exceed 90% of physical memory minus total SGA.

To see the total SGA:

SELECT ROUND(SUM(value)/1024/1024) "TOTAL SGA (MB)" FROM V$SGA;

Purpose:

Target: Used for managing performance and tuning automatic memory usage in work areas (sorts, hashes).

Limit: Used to prevent overall PGA consumption from taking down the server by exhausting physical memory.

Monitor:

See Sizing estimated performance:
SELECT * FROM V$PGA_TARGET_ADVICE 

 ORDER BY pga_target_for_estimate;

 

See Current Usage:

SELECT ROUND(value/1024/1024) AS curr_pga_target_mb 

  FROM V$PGASTAT 

 WHERE name = 'aggregate PGA target parameter';

Maximum instance PGA allocated in megabytes since the database was started:

SELECT ROUND(value/1048576) as value_mb 

  FROM V$PGASTAT 

 WHERE name='maximum PGA allocated';

If AMM is used

If AMM (Automatic Memory Management) is used ( == MEMORY_TARGET set),  only the memory_target is set, and Oracle manages the PGA, and this parameter acts as a minimum.

With MEMORY_TARGET set, the SGA_TARGET setting becomes the minimum size of the SGA and the PGA_AGGREGATE_TARGET setting becomes the minimum size of the instance PGA.

By setting both of these to zero, there are no minimums, and the SGA and instance PGA can grow as needed.

The sizing of SQL work areas remains automatic.

In addition, you can use the PGA_AGGREGATE_LIMIT initialization parameter to set an instance-wide hard limit for PGA memory. You can set PGA_AGGREGATE_LIMIT whether or not you use automatic memory management. 

For AMM use:

SELECT * FROM V$MEMORY_TARGET_ADVICE ORDER BY memory_size;

If you are hitting ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT error message you have 2 options
Option 1:
Set the value of pga_aggregate_limit to 0 and the parameter will not have any impact.

It behave like pre12c database.

ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SCOPE=BOTH;

Option 2:
Set this value to a higher value if you have enough physical memory on your system by using below command the server
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=6000M SCOPE=BOTH;

No need restart the database, as this is a dynamic parameter.

==========================================

How to know if pga_aggregate_limit is set ot not?

==========================================

CREATE PFILE='/software/oracle/122/dbs/pfileigt.ora_20231121' FROM SPFILE='/software/oracle/122/dbs/spfileigt.ora';

oracle@qanfv-2-dbs-01:~>% less /software/oracle/122/dbs/pfileigt.ora_20231121 | grep -i pga

igt.__pga_aggregate_target=18790481920

*._pga_max_size=5368709120

*.pga_aggregate_target=15728640000

SQL> SHOW PARAMETER PGA_AGGREGATE

NAME                         VALUE

---------------------------- --------------

pga_aggregate_limit          56G

pga_aggregate_target         15000M

SQL> SHOW PARAMETER MEMORY

NAME                         VALUE

---------------------------- --------------

memory_max_target            56G

memory_target                50G

PGA_AGGREGATE_LIMIT Theory

PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.

Default value

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 at least 5MB times the PROCESSES parameter for an Oracle RAC instance).

Range of values

Do not attempt to set PGA_AGGREGATE_LIMIT below its default value, even in a parameter file (pfile), or instance startup will fail. 

However, PGA_AGGREGATE_LIMIT can be set to 0 either in a parameter file or dynamically after startup. 

If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.