oracle pga 查询

SELECT  SS.SID,SS.USERNAME,SS.PROCESS,PP.NAME,PP.MM FROM (

(select * from v$session t where t.USERNAME like 'BM%')  SS  JOIN  

(select a.sid,b.name,(round(a.value/1024/1024,2))||'M' AS MM from v$sesstat a,v$statname b

where ( b.name like '%pga%') and a.statistic# = b.statistic#)  PP ON  SS.SID=PP.SID)

ORDER BY SS.USERNAME

 

=======================================================================

SELECT  SS.SID,SS.USERNAME,SS.PROCESS,PP.session_pga_memory,PP.session_pga_memory_max FROM (

(select * from v$session t where t.USERNAME like 'BM%')  SS  JOIN  

(

select spm.sid,spm.name,spm.session_pga_memory,spmx.session_pga_memory_max from 

(select a.sid,b.name,(round(a.value/1024/1024,2))||'M' AS session_pga_memory from v$sesstat a,v$statname b

where ( b.name like '%pga%') and a.statistic# = b.statistic# and b.name='session pga memory')  spm

join

(select a.sid,b.name,(round(a.value/1024/1024,2))||'M' AS session_pga_memory_max from v$sesstat a,v$statname b

where ( b.name like '%pga%') and a.statistic# = b.statistic# and b.name='session pga memory max')  spmx

on spm.sid = spmx.sid 

 

)  PP ON  SS.SID=PP.SID)

ORDER BY SS.USERNAME

 

=======================================================================

select username,sum(session_pga_memory)||'MB',sum(session_pga_memory_max)||'MB' from (

SELECT  SS.SID,SS.USERNAME,SS.PROCESS,PP.session_pga_memory,PP.session_pga_memory_max FROM (

(select * from v$session t where t.USERNAME like 'BM%')  SS  JOIN  

(

select spm.sid,spm.name,spm.session_pga_memory,spmx.session_pga_memory_max from 

(select a.sid,b.name,(round(a.value/1024/1024,2)) AS session_pga_memory from v$sesstat a,v$statname b

where ( b.name like '%pga%') and a.statistic# = b.statistic# and b.name='session pga memory')  spm

join

(select a.sid,b.name,(round(a.value/1024/1024,2)) AS session_pga_memory_max from v$sesstat a,v$statname b

where ( b.name like '%pga%') and a.statistic# = b.statistic# and b.name='session pga memory max')  spmx

on spm.sid = spmx.sid 

 

)  PP ON  SS.SID=PP.SID)

) group by username;