Instance allocated memory (SGA+PGA)

Checking oracle usage memory is quite difficult on unix:
when using 'ps aux' , RSS shows process memory + shared memory for each process.
You need to use svmon or pmap to substract the shared part.

This scripts shows process memory (PGA) and instance memory (SGA) and shows quickly the most memory consumming processes.

The sum gives the total memory used by the instance on the server.
SELECT 'PGA: pid ' || p.spid pid,
p.pga_alloc_mem bytes,
p.username ps_user,
p.program ps_program,
s.logon_time,
s.sid,
s.serial#,
s.username,
s.machine,
s.osuser,
s.program
FROM v$session s, v$sesstat pcur, v$process p
WHERE pcur.statistic# = 20
AND s.paddr = p.addr
AND pcur.sid = s.sid
UNION ALL
SELECT 'SGA: ' || name pid,
value bytes,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
FROM v$sga
ORDER BY 1 DESC;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45259/viewspace-134951/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45259/viewspace-134951/