Monitor Per Session UGA, PGA and Cursor Usage
set pages500 lines110 trims onclear 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
页:
[1]