About

Martin Klier

usn-it.de

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

Make Linux Disk IDs Visible for UDEV (in VMware)
Very cool interview with Maria Colgan

3 thoughts on “Oracle PGA Analysis Query

  1. think this stops subquery problem but only first line of SQL text

    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,
    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,
    (select sql_text from gv$sqltext sa where s.sql_id=sa.sql_id and sa.piece = 0) as SQL_TEXT,
    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_text from gv$sqltext sa where s.prev_sql_id=sa.sql_id and sa.piece = 0) as prevSQL_TEXT,
    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=124 — comment this out if all
    AND s.username IS NOT NULL — comment out to include background tasks
    order by PGA_USED_MB desc nulls last;

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.