-- 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;
-- 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