Performance - Memory

• SGA breakdown
• PGA usage by username
• Display pool usage

SGA breakdown

set lines 100 pages 999
col bytes format 999,999,999
compute sum of bytes on pool
break  on pool skip 1
select pool
, name
, bytes
from  v$sgastat
order  by pool
, name
/

PGA usage by username

select st.sid "SID",
 sn.name "TYPE",
 ceil(st.value / 1024 / 1024) "MB"
from v$sesstat st,
 v$statname sn
where st.statistic# = sn.statistic#
and  sid in (select sid
  from v$session
  where username like '&user')
and upper(sn.name) like '%PGA%'
order by st.sid,
  st.value desc
/

Display pool usage

select name
, sum(bytes)
from v$sgastat
where pool like 'shared pool'
group by name
/