PGA / UGA memory per session


select *
from (SELECT ssn.sid,
nvl(ssn.username, nvl(bgp.name, 'background')) as username,
SSN.STATUS,
ssn.machine,
ins.host_name,
prc.spid as PID_THREAD,
round((se1.value / 1024) / 1024, 4) as CURRENT_PGA_SIZE_MB,
round((se2.value / 1024) / 1024, 4) as MAXIMUM_PGA_SIZE_MB,
round((se3.value / 1024) / 1024, 4) as CURRENT_UGA_SIZE_MB,
round((se4.value / 1024) / 1024, 4) as MAXIMUM_UGA_SIZE_MB
FROM v$sesstat   se1,
v$sesstat   se2,
v$sesstat   se3,
v$sesstat   se4,
v$session   ssn,
v$bgprocess bgp,
v$process   prc,
v$instance  ins,
v$statname  stat1,
v$statname  stat2,
v$statname  stat3,
v$statname  stat4
WHERE se1.statistic# = stat1.statistic#
and stat1.name = 'session pga memory'
AND se2.statistic# = stat2.statistic#
and stat2.name = 'session pga memory max'
and se3.statistic# = stat3.statistic#
and stat3.name = 'session uga memory'
AND se4.statistic# = stat4.statistic#
and stat4.name = 'session uga memory max'
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND se3.sid = ssn.sid
AND se4.sid = ssn.sid
AND ssn.paddr = bgp.paddr(+)
AND ssn.paddr = prc.addr(+)) WHERE USERNAME = 'IB'
order by USERNAME, STATUS, CURRENT_PGA_SIZE_MB desc

 

Leave a Reply

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