oracle监控pga,oracle pga使用情况常用脚本：

1.Look at trends of individual processes growing in size: (查看某个进程pga增长的趋势)

select p.spid, s.sid, substr(n.name,1,25) memory, s.value as Bytes from v$sesstat s, v$statname n, v$process p, v$session vs

where s.statistic# = n.statistic#

/* this query currently looks at both uga and pga, if only one of these is desired modify the like clause to pga or uga */

and n.name like '%ga memory%'

and s.sid=vs.sid

and vs.paddr=p.addr

/* --remove comment delimiters to view only certain sizes, i.e. over 10Mbytes

and s.value > 10000000 */

order by s.value asc;

2.列出占用pga最大的进程

select pid,spid,substr(username,1,20) "USER" ,program,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM

from v$process

where pga_alloc_mem=(select max(pga_alloc_mem) from v$process

where program not like '%LGWR%');

3.在v$process中pga的总和

select sum(pga_alloc_mem)/1024/1024 as "Mbytes allocated", sum(PGA_USED_MEM)/1024/1024 as "Mbytes used" from v$process;

4.基于   v$sesstat     中pga的总和

select sum(value)/1024/1024 as Mbytes from v$sesstat s, v$statname n

where

n.STATISTIC# = s.STATISTIC# and

n.name = 'session pga memory';

5.查看pga的详细情况

select substr(name,1,30), value, unit from v$pgastat;

6.查看数据库会话所有sid占用pga的的详细情况

set linesize 120

set pagesize 120

column spid heading 'OSpid' format a8

column pid heading 'Orapid' format 999999

column sid heading 'Sess id' format 99999

column serial# heading 'Serial#' format 999999

column status heading 'Status' format a8

column pga_alloc_mem heading 'PGA alloc' format 99,999,999,999

column pga_used_mem heading 'PGA used' format 99,999,999,999

column username heading 'oracleuser' format a12

column osuser heading 'OS user' format a12

column program heading 'Program' format a20

SELECT

p.spid,

p.pid,

s.sid,

s.serial#,

s.status,

p.pga_alloc_mem,

p.PGA_USED_MEM,

s.username,

s.osuser,

s.program

FROM

v$process p,

v$session s

WHERE s.paddr ( + ) = p.addr

and p.BACKGROUND is null /* Remove if need to monitor background processes */

Order by p.pga_alloc_mem desc；

7.pga和sga 在oracle实例的使用的总和

select sum(bytes)/1024/1024 as Mbytes from

(select value as bytes from v$sga

union all

select value as bytes from

v$sesstat s,

v$statname n

where

n.STATISTIC# = s.STATISTIC# and

n.name = 'session pga memory'

);

参考：http://blog.itpub.net/23135684/viewspace-712768/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25462274/viewspace-2123370/，如需转载，请注明出处，否则将追究法律责任。