Sign in or 
Share this
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
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
olapdba |
Latest page update: made by olapdba
, Feb 13 2008, 11:12 AM EST
(about this update
About This Update
Edited by olapdba
view changes - complete history) |
|
Keyword tags:
None
More Info: links to this page
|
There are no threads for this page.
Be the first to start a new thread.
