ORA-04036 PGA memory exceeds PGA_AGGREGATE_LIMIT

Although the PGA_AGGREGATE_LIMIT is set and Oracle decided to kill the process/session that was using so much, it sometimes still exceeds and grows and eventually exhausting all the memory.

SQL> show parameter pga_aggregate_limit
NAME                            TYPE        VALUE
------------------------------- ----------- ------------------------------
pga_aggregate_limit             big integer 14G

SQL> select round(pga_used_mem/1024/1024) PGA_USED_MEM
from v$process p join v$session s on p.addr = s.paddr
where s.sid = 259;

PGA_USED_MEM
------------
14928

SQL> /

PGA_USED_MEM
------------
15015

From the alert log:

KILL SESSION for sid=(259, 29568):
   Reason = alter system kill session
   Mode = KILL HARD SAFE -/-/-
   Requestor = USER (orapid = 25, ospid = 4952, inst = 1)
   Owner = Process: USER (orapid = 148, ospid = 5628)
   Result = ORA-31

The culprit here is the ORA-31 error. This is the ‘marked for kill’ message. So the process is not killed, but only marked for kill:

select sid, serial#, status from v$session where sid = 259;
sid    serial# status
---    ------- ------
259    29568   KILLED

This means it will wait until the process/session is done with it’s uninterruptible work, before it’s really killed. If this takes long, it will keep eating up memory until your host has no memory left.

ORA-00031: session marked for kill

00031. 00000 -  "session marked for kill"
 *Cause:    The session specified in an ALTER SYSTEM KILL SESSION command
        cannot be killed immediately (because it is rolling back or blocked
        on a network operation), but it has been marked for kill.  This
        means it will be killed as soon as possible after its current
        uninterruptable operation is done
 Action:   No action is required for the session to be killed, but further
        executions of the ALTER SYSTEM KILL SESSION command on this session
        may cause the session to be killed sooner.

For Windows there is an other ‘official’ way to get rid of this, and that;s using the `orakill` command:

select p.spid
from v$process p
join v$session s on p.addr = s.paddr
where s.sid = 259    and s.serial# = 29568;

spid
----
5628

> orakill <SID> 5628

It will take a few minutes, but then the process/session is gone and memory is freed!

Tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *