Thursday, June 28, 2012

Oracle DBA script: List the UGA and PGA used by each session

column name format a25
column total format 999 heading 'Cnt'
column bytes format 9999,999,999 heading 'Total Bytes'
column avg format 99,999,999 heading 'Avg Bytes'
column min format 99,999,999 heading 'Min Bytes'
column max format 9999,999,999 heading 'Max Bytes'
ttitle 'PGA = dedicated server processes - UGA = Client machine process'

compute sum of minmem on report
compute sum of maxmem on report
break on report

select se.sid,, 
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by,se.sid
order by 3

No comments:

Post a Comment