Oracle PGA Analysis Query

Sometimes, something in an Oracle Database starts eating PGA, and there is need to find out which process, and where the memory is left, and what or who is responsible for allocating / using / holding it.

For a recent issue, I wrote the SQL below. It gives back one line for each Category (SQL, PL/SQL, Freeable, Other) in V$PROCESS_MEMORY per process, and enriches it with SQL Statements from the corresponding sessions. Let me know if it is helpful for you.

--------------------------------------------------------------------
-- PGA Realtime Analysis Query
-- Initial: Martin Klier, Performing Databases, 2020-05-19
--------------------------------------------------------------------
-- Change: none
-- Keywords: PGA, Cursors, PGA Usage, PGA Allocated, Memory Usage
--------------------------------------------------------------------

-- Purpose: Find what or who is allocating PGA and other memory, real-time only.

-- Important: For each entry in V$PROCESS_MEMORY, we get one line (thus, we work by CATEGORY)

-- Serving suggestion: Run it repeatedly and preserve results (aka Create a history) for long-term observations.

select  systimestamp,
        pm.pid,
        pm.category,
        round(pm.allocated/1024/1024,1) as PGA_ALLOCATED_MB,
        round(pm.used/1024/1024,1) as PGA_USED_MB,
        round(pm.max_allocated/1024/1024,1) as PGA_MAX_ALLOCATED_MB,
        c.num_cursors as SESS_OPEN_CURSORS,
        s.sid,
        s.serial#,
        s.username,
        s.machine,
        s.program,
        s.module,
        s.action,
        s.sql_id,
        (select count(*) from v$sql sq where sq.sql_id=s.sql_id) as SQL_LC_CURSORS,
        s.prev_sql_id,
        (select count(*) from v$sql sq where sq.sql_id=s.prev_sql_id) as PREV_SQL_LC_CURSORS,
        (select sql_fulltext from v$sqlarea sa where s.sql_id=sa.sql_id) as SQL_FULLTEXT,
        (select sql_fulltext from v$sqlarea sa where s.prev_sql_id=sa.sql_id) as PREV_SQL_FULLTEXT,
        p.tracefile,
        '-- alter system kill session '''||s.sid||','||s.serial#||''' immediate;' as KILL_SESSION_COMMAND
from V$PROCESS_MEMORY pm, 
    v$PROCESS p, 
    v$session s,
    (SELECT ss.sid, ss.value as num_cursors FROM v$sesstat ss, v$statname sn WHERE ss.statistic# = sn.statistic#  AND sn.name = 'opened cursors current') c
where pm.pid=p.pid
    and s.paddr=p.addr
    and s.sid=c.sid
    --and p.pid=89
order by PGA_USED_MB desc nulls last
--order by PGA_ALLOCATED_MB desc nulls last
--order by PGA_MAX_ALLOCATED_MB nulls last
--order by SESS_OPEN_CURSORS desc nulls last
;

Stay safe, and keep your memory eaters at bay.

Yours, Martin