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

Product: Oracle Database
Version: 12.1.0.2

Oracle 12c introduced a new parameter called PGA_Aggregate_Limit, which is a global PGA memory control to prevent RAM contention and virtual memory swapping to disk.

Previous parameter PGA_Aggregate_Target is only the prefer global PGA usage for the entire database instance, while some customers have been experiencing RAM contention due to this is a soft limit.

There is a known bug for PGA_Aggregate_Limit even you disable it by setting to 0.  You will continue to see following error:

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

This bug will be visible on following conditions:

1. Supplemental Logging is enable for the table
2. Updating of Spatial column
3. After install an unknown version of PSU or CPU patch (DBA does not disclose exact version)

The way to troubleshoot whether you affected by this known Oracle bug is as below:

1. In alert.log, confirm you are getting ORA-04036 which killing some DB sessions
2. Execute following from sqlplus

select value/power(1024,2) "MB" from v$pgastat where name = 'maximum PGA allocated';

2.1. Confirms it exceeded PGA_Aggregate_Limit size set in spfile.ora/pfile.ora

2.2. If it is below, then you are very likely hitting this Oracle bug

3. Review the generated trc log as shown in alert.log

3.1. The log will show the PGA memory usage

3.2. If it is below, then you are very likely hitting this Oracle bug

4. Determine the failing SQL statement and execute following from sqlplus and fill in the table name

SQL> desc user_log_groups

 Name                                      Null?    Type

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

 OWNER                                     NOT NULL VARCHAR2(128)

 LOG_GROUP_NAME                            NOT NULL VARCHAR2(128)

 TABLE_NAME                                NOT NULL VARCHAR2(128)

 LOG_GROUP_TYPE                                     VARCHAR2(19)

 ALWAYS                                             VARCHAR2(11)

 GENERATED                                          VARCHAR2(14)

SQL> select log_group_name, table_name from user_log_groups where table_name = '&table_name';

Note: Replace user_log_groups with dba_log_groups for all users

SQL> desc user_log_group_columns

 Name                Null?    Type

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

 OWNER               NOT NULL VARCHAR2(128)

 LOG_GROUP_NAME      NOT NULL VARCHAR2(128)

 TABLE_NAME          NOT NULL VARCHAR2(128)

 COLUMN_NAME                  VARCHAR2(4000)

 POSITION                     NUMBER

 LOGGING_PROPERTY             VARCHAR2(6)

SQL> select log_group_name, table_name, column_name from user_log_group_columns;

Note: Replace user_log_group_columns with dba_log_group_columns for all users

5. Confirm that the failing table contains spatial column

SQL> select data_type, table_name, owner from user_tab_columns where data_type = 'SDO_GEOMETRY';

DATA_TYPE       TABLE_NAME      OWNER

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

SDO_GEOMETRY    CONTACTADDRESS  TEST1

Note: Replace user_tab_columns with dba_tab_columns  for all users

Workaround

Drop the supplemental logging for the failing table.  Several different syntax are listed below to make it more general

SQL> ALTER TABLE DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

SQL> ALTER TABLE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

SQL> ALTER TABLE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

SQL> ALTER TABLE DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

Note: The first statement should be sufficient, while others are for specific use case

If you want to disable it globally, then use following statement:

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;