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
- The source also contains a longer SQL*Plus script that summarises PGA, UGA and SGA usage. It depends on local include scripts, so it is not reproduced here.
- The per-session PGA query is adapted from an article on Oracle memory parameters: https://easyteam.fr/les-parametres-de-la-memoire-oracle/