獲取指定pid的session的pga/uga使用情況

unix平臺下使用top

windows平臺下可以透過實用工具pstat.exe

獲取相應程式/執行緒的pid.

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 s.username,se.sid,n.name,
 max(se.value) maxmem
 from v$sesstat se,
 v$statname n,
 v$session s
 where n.statistic# = se.statistic#
 and n.name in ('session pga memory','session pga memory max',
 'session uga memory','session uga memory max')
 and s.sid = se.sid
 and s.username is not null
 and s.paddr = (SELECT addr
                               FROM v$process c
                               WHERE c.spid = TO_NUMBER ('&pid', 'xxxx'))
 group by s.username,n.name,se.sid
 order by 3
/