LLI Wiki — My Digital Knowledge Base

Query session and instance PGA and SGA memory in Oracle

V$ queries to see PGA memory per session and the total SGA and PGA.

These queries show the PGA and SGA memory used by the sessions and by the instance. Run them as a user with access to the V$ views.

PGA memory per session

SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
       nvl(lower(ssn.machine), ins.host_name) "SESSION",
       to_char(prc.spid, '999999999') "PID/THREAD",
       to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' "CURRENT SIZE",
       to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' "MAXIMUM SIZE"
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
     v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# AND stat1.name = 'session pga memory'
  AND se2.statistic# = stat2.statistic# AND stat2.name = 'session pga memory max'
  AND se1.sid = ssn.sid
  AND se2.sid = ssn.sid
  AND ssn.paddr = bgp.paddr (+)
  AND ssn.paddr = prc.addr (+);

Total SGA

select sum(value) from v$sga;

Example result: 2147483648 (2 GB).

Total PGA allocated

select name, value from v$pgastat where name like 'total PGA a%';

Example result: total PGA allocated = 430492672.

Notes

Mastodon LLIMastodon SocialFosstodon