Pga Nightmare in Oracle 19c database -- MGA / ORA-04030 / ORA-04036

Had  to  write this  Blog  Considering Lot of Pga issues we  been facing   after migration from 12c to 19c . 

This is  because   MGA is also  Part of   Pga  now .    To get more information on  Mga please refer  2638904.1 

In Oracle release 12.1:    Pga  setting was  the greater of the following:

1. 2 GB 

2. 200% of PGA_AGGREGATE_TARGET 

3. (Value of PROCESSES initialization parameter setting) * 3 MB

It will not exceed 120% of the physical memory size minus the total SGA size.

In Oracle release 12.2:   Pga  setting was as per below 

* 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 for standby  database , and for Rac database it should be at least 5MB times the PROCESSES parameter

From 19c we need to  add Mga overhead to Pag  using below formula .   Please refer to metalink document 2808761.1  for more information . 

PGA_AGGREGATE_LIMIT = (original PGA_AGGREGATE_LIMIT value) + ((maximum number of connected processes) * 4M)

Sql  used for analysis . 

select * from dba_hist_pgastat where name ='total PGA allocated' order by snap_id;

set lines 200;

set pages 200;

column name format a25;

column pname format a12;

column "MegaBytes" format a10;

set numwidth 6;

select s.sid, s.serial#,p.pid, p.spid,p.pname, sn.name, round(ss.value/(1024 *1024))||'Mb' "MegaBytes"

from v$sesstat ss, v$statname sn, v$session s, v$process p

where s.paddr = p.addr

and sn.statistic# = ss.statistic#

and s.sid = ss.sid

and sn.name in ('session pga memory' , 'session pga memory max')

and p.pname like 'DIA%'

order by ss.value

/

col max_pga for 99999999.9

select value/1024/1024 max_pga from v$pgastat where name='maximum PGA allocated' minus

     select value/1024/1024 max_pga from v$pgastat where name='MGA allocated (under PGA)';

select max_utilization from v$resource_limit where resource_name='processes';

set pages 333 lines 255

set wrap off

col "Sid/Serial" for a12

col UnixPid for a8

col OraUser for a8

col OsUser for a8

col machine for a18

col Minutes for 99990.9

col "ClientProgram" format a28

col "ServerProgram" format a32

col "Program" format a25

col "Module" format a25

col "SQL Query" format a60

col "MBmem" format 99999

col "App PID" for a8

col "SPID" for a8

BREAK ON REPORT

COMPUTE SUM LABEL "Total PGA MB" OF  "MBmem" ON REPORT

select /* Running SQL */ distinct 

       s.INST_ID "INST_ID",s.sid||','||s.serial# "Sid/Serial", s.sql_id,p.spid "SPID",s.process "App PID", s.username "DBUser",s.osuser "OSUser", s.machine,

       s.last_call_et "Time(Sec)", 

       q.sql_text "SQL Query", p.pga_alloc_mem/1024/1024 "MBmem"

from gv$session s,gv$process p, gv$sql q

where s.INST_ID=p.INST_ID and

      s.paddr=p.addr and

      s.sql_id=q.sql_id(+)

and s.username is not null

and s.sql_id is not null

and s.status <> 'INACTIVE'

and q.sql_text not like '%Running SQL%'

order by "INST_ID","Time(Sec)" desc;

 List of Related Bug :

Bug 32521805 : ORA-4030 IN MGA, MGA 20% OF PGA_AGGREGATE_LIMIT

BUG 30611650 - HIGH PGA USAGE FOR A QUERY USING XMLFOREST was filed for this kind of problem.

Bug 30028599 - ORA-4036: PGA memory used by the instance exceeds pga_aggregate_limit (Doc ID 30028599.8)

Database Failures and Hangs in RAC due to MGA Sizing and Allocation Issues (Doc ID 2831121.1)

Related  Metalink Document : 

Sizing the PGA in Oracle 19c - How to Account for the MGA Size (Doc ID 2808761.1)

MGA (Managed Global Area) Reference Note (Document 2638904.1)

Cannot Increase or Decrease The Value of PGA_AGGREGATE_LIMIT on 19c (Doc ID 2685564.1)

MMON SLAVE Process Consuming More PGA Memory in 19c (Doc ID 2721971.1)