Oracle Session Memory Usage

 Contents of this Post

1. Viewing Memory Use for Each User Session
2. Memory Usage In Detail

Viewing Memory Use for Each User Session

set lines 200 pages 200
col "Current UGA memory" for a30

SELECT    USERNAME
                  ,sess.SID
                  ,sess.SERIAL#
                  ,VALUE || ' bytes = ' ||round(VALUE/1024/1024,2) ||' MB '  "Current UGA memory"
FROM       V$SESSION sess
                  ,V$SESSTAT stat
                  ,V$STATNAME name
WHERE     sess.SID = stat.SID
AND          stat.STATISTIC# = name.STATISTIC#
AND          name.NAME = 'session uga memory'
--AND       USERNAME= 'ORACLE'
--And         Value >= 10485760                (All Session Usage > 10MB)
order by VALUE;



Memory Usage In Detail
A  script to display the memory used by any specific session

select
    to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username,
    nvl(bgp.name, 'background')) || nvl(lower(ssn.machine), ins.host_name) ssession,
    to_char(prc.spid, '999999999')                       pid_thread,
    to_char((se1.value / 1024) / 1024, '999g999g990d00') current_size_mb,
    to_char((se2.value / 1024) / 1024, '999g999g990d00') maximum_size_mb
from
    v$statname  stat1,
    v$statname  stat2,
    v$session   ssn,
    v$sesstat   se1,
    v$sesstat   se2,
    v$bgprocess bgp,
    v$process   prc,
    v$instance  ins
where    stat1.name = 'session pga memory'
and    stat2.name = 'session pga memory max'
and     se1.sid = ssn.sid
and     se2.sid = ssn.sid
and     se2.statistic# = stat2.statistic#
and     se1.statistic# = stat1.statistic#
and     ssn.paddr = bgp.paddr(+)
and     ssn.paddr = prc.addr(+)
--and    ssn.sid in (select sid from v$session where username=    'ORACLE')
order by MAXIMUM_SIZE_MB;