isitdevops.com/databases

—
— This Query displays full PGA and UGA memory info for all sessions.
—
— Oracle 10g version.
—

select decode(nvl(length(z.username),0),
              0,
              substr(z.PROGRAM,instr(z.program,'(‘),20),
              substr(z.username,1,20)) “Username”,
       substr(to_char(z.sid),1,4) “SID”,
       s_pga.pga “PGA”,
       s_pgam.pgam “PGA Max”,
       (s_pgam.pgam – s_pga.pga) “PGA Diff”,
       s_uga.uga “UGA”,
       s_ugam.ugam “UGA Max”,
       (s_ugam.ugam – s_uga.uga) “UGA Diff”,
       s_tota.tota “(UGA + PGA)”,
       ((s_ugam.ugam – s_uga.uga) + (s_pgam.pgam – s_pga.pga)) “(UGA + PGA) Diff”
from (select b.sid, b.value uga
      from
       v$sesstat b
      where
       b.statistic# = 20) s_uga,
     (select b.sid, b.value ugam
      from
       v$sesstat b
      where
       b.statistic# = 21) s_ugam,
     (select b.sid, b.value pga
      from
       v$sesstat b
      where
       b.statistic# = 25) s_pga,
     (select b.sid, b.value pgam
      from
       v$sesstat b
      where
       b.statistic# = 26) s_pgam,
     (select b.sid, sum(b.value) tota
      from
       v$sesstat b
      where
       b.statistic# in (20,25)
      group by b.sid) s_tota,
       v$session z
where z.sid = s_uga.sid and
      z.sid = s_pga.sid and
      z.sid = s_tota.sid and
      z.sid = s_ugam.sid and
      z.sid = s_pgam.sid
union
select ‘** TOTALS **’,
       substr(‘Count = ‘ || to_char(count(z.sid)),1,15),
       sum(s_pga.pga) “PGA”,
       sum(s_pgam.pgam) “PGA Max”,
       sum(s_pgam.pgam – s_pga.pga) “PGA Diff”,
       sum(s_uga.uga) “UGA”,
       sum(s_ugam.ugam) “UGA Max”,
       sum(s_ugam.ugam – s_uga.uga) “UGA Diff”,
       sum(s_tota.tota) “(UGA + PGA)”,
       sum((s_ugam.ugam – s_uga.uga) + (s_pgam.pgam – s_pga.pga)) “(UGA + PGA) Diff”
from (select b.sid, b.value uga
      from
       v$sesstat b
      where
       b.statistic# = 20) s_uga,
     (select b.sid, b.value ugam
      from
       v$sesstat b
      where
       b.statistic# = 21) s_ugam,
     (select b.sid, b.value pga
      from
       v$sesstat b
      where
       b.statistic# = 25) s_pga,
     (select b.sid, b.value pgam
      from
       v$sesstat b
      where
       b.statistic# = 26) s_pgam,
     (select b.sid, sum(b.value) tota
      from
       v$sesstat b
      where
       b.statistic# in (20,25)
      group by b.sid) s_tota,
       v$session z
where z.sid = s_uga.sid and
      z.sid = s_pga.sid and
      z.sid = s_tota.sid and
      z.sid = s_ugam.sid and
      z.sid = s_pgam.sid
order by 4 desc
/