How to Monitor UGA, PGA and Cursor Usage Per Session. [ID 551198.1]

How to Monitor UGA, PGA and Cursor Usage Per Session. [ID 551198.1] Modified 26-MAY-2008 Type HOWTO Status MODERATED In this Document Goal Solution References <

leishifei

381人浏览 · 2011-04-28 12:48:00

leishifei · 2011-04-28 12:48:00 发布

How to Monitor UGA, PGA and Cursor Usage Per Session. [ID 551198.1]

		Modified 26-MAY-2008 Type HOWTO Status MODERATED

In this Document
  Goal
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1
Information in this document applies to any platform.

Goal

The next query provides a way to monitor the UGA, PGA and Cursor Usage per Session.

Solution

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;

Sample Output:

USERNAME	NAME	VALUE
DBSNMP:129,129	opened cursors cumulative	3528
	opened cursors current	21
	session cursor cache count	20
	session cursor cache hits	3441
	session pga memory	2092628
	session pga memory max	2682452
	session stored procedure space	0
	session uga memory	745508
	session uga memory max	2234408
DBSNMP:145,10	opened cursors cumulative	193548
	opened cursors current	9
	session cursor cache count	20
	session cursor cache hits	54415
	session pga memory	2616916
	session pga memory max	18738772
	session stored procedure space	0
	session uga memory	976012
	session uga memory max	4690736
SYS:135,1244	opened cursors cumulative	6
	opened cursors current	1
	session cursor cache count	4
	session cursor cache hits	0
	session pga memory	454228
	session pga memory max	454228
	session stored procedure space	0
	session uga memory	156332
	session uga memory max	156332
SYSMAN:130,14	opened cursors cumulative	196889
	opened cursors current	100
	session cursor cache count	19
	session cursor cache hits	124940
	session pga memory	18459600
	session pga memory max	19835856
	session stored procedure space	0
	session uga memory	16653260
	session uga memory max	17960720
SYSMAN:131,2	opened cursors cumulative	66604
	opened cursors current	53
	session cursor cache count	20
	session cursor cache hits	66440
	session pga memory	2420308
	session pga memory max	3927636
	session stored procedure space	0
	session uga memory	1073032
	session uga memory max	1524116
SYSMAN:132,6	opened cursors cumulative	121
	opened cursors current	25
	session cursor cache count	20
	session cursor cache hits	66068
	session pga memory	2223700
	session pga memory max	2420308
	session stored procedure space	0
	session uga memory	745508
	session uga memory max	745508
SYSMAN:142,36	opened cursors cumulative	7
	opened cursors current	0
	session cursor cache count	6
	session cursor cache hits	1
	session pga memory	454228
	session pga memory max	454228
	session stored procedure space	0
	session uga memory	156332
	session uga memory max	221796
SYSTEM:139,12	opened cursors cumulative	63
	opened cursors current	1
	session cursor cache count	19
	session cursor cache hits	36
	session pga memory	847444
	session pga memory max	847444
	session stored procedure space	0
	session uga memory	221796
	session uga memory max	221796