oracle pga 查询

最新推荐文章于 2024-01-23 14:37:16 发布

iteye_897

最新推荐文章于 2024-01-23 14:37:16 发布

阅读量429

点赞数

分类专栏：数据库基础文章标签：数据库

本文链接：https://blog.youkuaiyun.com/iteye_897/article/details/82446695

版权

18 篇文章

订阅专栏

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;