Thursday, April 19, 2018

Memory Usage by Sessions

-- Memory Usage 


-- check PGA memory stats 

select * from v$pgastat;

-- memory used by sessions

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(+)
ORDER BY 4;

-- Memory used by group of sessions

SELECT ssn.sid,
         ssn.username,
         SUM ( (se1.VALUE / 1024) / 1024) Current_size,
         SUM ( (se2.VALUE / 1024) / 1024) 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(+)
GROUP BY ssn.sid, ssn.username
ORDER BY 4 DESC;

No comments:

Post a Comment