APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 28-Dec-2013***

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

REFERENCES

NOTE:469521.1 - Which Views Can Be Used to Monitor PGA Usage in Oracle 10g and Higher?
(文件 ID 551198.1)

How to Monitor UGA, PGA and Cursor Usage Per Session

APPLIES TO:

GOAL

SOLUTION

REFERENCES

相關文章