Monitor Per Session UGA, PGA and Cursor Usage

set pages500 lines110 trims on
clear col
col name format a30
col username format a20
break on username nodup skip 1

select vses.username||':'||vsst.sid||','||vses.serial# username, vstt.name, max(vsst.value) value
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and vstt.name in
('session pga memory','session pga memory max','session uga memory','session uga memory max',
'session cursor cache count','session cursor cache hits','session stored procedure space',
'opened cursors current','opened cursors cumulative') and vses.username is not null
group by vses.username, vsst.sid, vses.serial#, vstt.name
order by vses.username, vsst.sid, vses.serial#, vstt.name;

USERNAME.............NAME................................VALUE
-------------------- ------------------------------ ----------
DBSNMP:121,778.......opened cursors cumulative...........38128
.....................opened cursors current.................10
.....................session cursor cache count.............50
.....................session cursor cache hits...........19974
.....................session pga.memory................1636504
.....................session pga.memory max............3602584
.....................session stored procedure space..........0
.....................session uga memory................1086492
.....................session uga memory max............2771784

DBSNMP:128,4503......opened cursors cumulative...........43222
.....................opened cursors current..................2
.....................session cursor cache count.............48
.....................session cursor cache hits...........26647
.....................session pga memory................2619544
.....................session pga memory max...........35190936
.....................session stored procedure space..........0
.....................session uga memory................2002988
.....................session uga memory max............9976328

SYS:130,39674........opened cursors cumulative..............35
.....................opened cursors current..................1
.....................session cursor cache count.............11
.....................session cursor cache hits...............9
.....................session pga memory.................989452
.....................session pga memory max.............989452
.....................session stored procedure space..........0
.....................session uga memory.................235460
.....................session uga memory max.............235460

SYSMAN:114,4360......opened cursors cumulative..............80
.....................opened cursors current..................1
.....................session cursor cache count.............50
.....................session cursor cache hits...............6
.....................session pga memory................2619544
.....................session pga memory max............2619544
.....................session stored procedure.space..........0
.....................session uga memory................1936660
.....................session uga memory max............2133052
标签: 暂无标签
oraunix

写了 199 篇文章,拥有财富 1026,被 339 人关注

转播转播 分享分享 分享淘帖
回复

使用道具

成为第一个吐槽的人

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈