A helpful script to find sessions using large amounts of system memory:
SET LINESIZE 140
SET PAGESIZE 100
COL session HEADING 'SID - User - Client' FORMAT a35
COL current_size HEADING 'Current MB' FORMAT '999,999.99'
COL maximum_size HEADING 'Max MB' FORMAT '999,999.99'
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF current_size ON REPORT
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",
se1.value/1024/1024 current_size,
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 (+)
-- AND NVL(ssn.username, NVL(bgp.name, 'background')) = '<SCHEMA>'
ORDER BY 4
/
!free
exit
/
Sample output:
SID - User - Client PID/THREAD Current MB Max MB
----------------------------------- ---------- ----------- -----------
cut...
867 - XXXX - server01 9615 3.47 223.60
729 - XXXX - server01 9611 3.47 223.78
469 - XXXX - server01 3479 3.16 223.85
605 - XXXX - server03 4007 10.97 1,373.78
1039 - XXXX - server01 21216 11.16 1,373.97
448 - XXXX - server02 3852 10.22 1,378.91
-----------
Total 980.91
total used free shared buffers cached
Mem: 132085672 125739556 6346116 0 836680 24065240
-/+ buffers/cache: 100837636 31248036
Swap: 8193140 9968 8183172
