oracle pga 查询

查询特定用户PGA内存使用

最新推荐文章于 2024-06-16 23:42:25 发布

原创最新推荐文章于 2024-06-16 23:42:25 发布 · 448 阅读

CC 4.0 BY-SA版权

文章标签：

18 篇文章

订阅专栏

本文提供了一系列SQL查询示例，用于查询Oracle数据库中特定用户名（以'BM%'为模式匹配）的会话PGA内存使用情况，包括每个会话的PGA内存及最大PGA内存，并汇总了所有相关会话的总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;