ORA-04036: PGA memory used by the instance...: Easy Guide

Today in this article, we will discuss how to resolve ORA-04036. Let’s go through this in detail :

Steps to Troubleshoot ORA-04036:

Step 1 :

Check if PGA_AGGREGATE_LIMIT is adequately sized.

--- Check current PGA usage
SELECT ROUND(SUM(pga_used_mem)/(1024*1024),2) PGA_USED_MB FROM v$process;

Step 2 :

Check the PGA used by all the sessions in DB/instance

COLUMN allocated     HEADING "Allocated MB" FORMAT 99999D9
COLUMN used          HEADING "Used MB"      FORMAT 99999D9
COLUMN free          HEADING "Free MB"      FORMAT 99999D9
COLUMN Max           HEADING "Max MB"       FORMAT 99999D9
COLUMN username   							FORMAT a18
COLUMN program                         		FORMAT a55
COLUMN sid                             		FORMAT a5
COLUMN spid                            		FORMAT a8
SET LINESIZE 300
SELECT s.username, SUBSTR(s.sid,1,5) sid, p.spid, logon_time,
       SUBSTR(s.program,1,60) program , s.process pid_remote,
       s.status,
       ROUND(pga_used_mem/1024/1024) used,
       ROUND(pga_alloc_mem/1024/1024) allocated,
       ROUND(pga_freeable_mem/1024/1024) free,
       ROUND(pga_max_mem/1024/1024) Max
FROM  v$session s,v$process p
WHERE p.addr=s.paddr
ORDER BY pga_max_mem,logon_time;

Step 3 :

From the above step 2, kill the process which are taking high PGA if possible. (be cautious, dont kill the background process, it will lead to Outage)

alter system kill session 'sid,serial#' immediate;

Step 4 :

Check the current uilization by Step1.

If none of the above steps work, then

i) set PGA_AGGREGATE_LIMIT to 0, which removes the limit on PGA usage per session

alter system set pga_aggregate_limit=0 scope=both;

ii) Otherwise, increase the size of PGA_AGGREGATE_LIMIT to a higher value.

alter system set pga_aggregate_limit=6G scope=both;

This is dynamic parameter. So database bounce is not required to change the value.

So, here are the steps by which you can easily troubleshoot the ORA-04036 error. Hope this helps!! Also, you can get further information from the Oracle Doc ID 2854122.1 as well.

Other ORA-error Related Articles :