Determine Process/PGA Memory in use by Process

Determine Process/PGA Memory in use by Process

If you would to determine the amount of memory database processes are consuming consider the following script

set lines 110
col unm format a30 hea "USERNAME (SID,SERIAL#)"
col pus format 999,990.9 hea "PROC KB|USED"
col pal format 999,990.9 hea "PROC KB|MAX ALLOC"
col pgu format 99,999,990.9 hea "PGA KB|USED"
col pga format 99,999,990.9 hea "PGA KB|ALLOC"
col pgm format 99,999,990.9 hea "PGA KB|MAX MEM"

select s.username||' ('||s.sid||','||s.serial#||')' unm, round((sum(m.used)/1024),1) pus,
round((sum(m.max_allocated)/1024),1) pal, round((sum(p.pga_used_mem)/1024),1) pgu,
round((sum(p.pga_alloc_mem)/1024),1) pga, round((sum(p.pga_max_mem)/1024),1) pgm
from v$process_memory m, v$session s, v$process p
where m.serial# = p.serial# and p.pid = m.pid and p.addr=s.paddr and
s.username is not null group by s.username, s.sid, s.serial# order by unm;

..........................PROC KB....PROC KB........PGA KB........PGA KB........PGA KB
USERNAME (SID,SERIAL#).......USED..MAX ALLOC..........USED.........ALLOC.......MAX MEM
---------------------- ---------- ---------- ------------- ------------- -------------
DBSNMP (120,1810)............64.4....1,882.9.......3,924.7.......6,845.9.......6,845.9
DBSNMP (121,778).............65.0....3,847.2.......6,116.8......11,709.9......15,549.9
DBSNMP (128,4503)............65.2...33,519.5.......9,184.8......14,013.9.....137,405.9
SYS (131,6970)..............111.9...29,879.6.......5,714.8.......8,670.3.....120,798.3
SYSMAN (119,7)...............21.1....1,649.5.......2,933.3.......4,558.4.......4,558.4
SYSMAN (122,11589)..........330.9...15,518.8......16,569.3......24,699.4......66,171.4
SYSMAN (126,17899)...........43.4....3,563.5.......8,667.4......14,781.9......17,597.9
SYSMAN (127,52)..........47,960.6...63,663.5.....176,985.5.....179,086.4.....190,990.4
SYSMAN (129,25990)...........49.7....3,099.9.......8,109.8......12,221.9......12,477.9
SYSMAN (132,10067)...........45.1....6,515.9......13,641.5......23,997.9......27,325.9
SYSMAN (133,6178)............46.7....3,615.5.......8,841.5......14,781.9......15,293.9
SYSMAN (134,13810)...........71.5....2,618.3.......5,555.3......10,429.9......10,429.9
SYSMAN (143,12587)...........47.5....2,670.7.......4,619.8.......9,405.9......10,941.9
SYSMAN (144,1858)...........107.0....2,431.7.......6,834.4......10,685.9......11,197.9
SYSMAN (146,6516)............41.6....2,277.6.......4,171.5.......8,125.9.......8,125.9
SYSMAN (147,11111)...........67.7....3,887.5.......8,309.8......13,501.9......19,389.9