查看pga的使用情况

jiawang

查看pga最大使用量：
select inst_id,name, ROUND(value/1024/1024) as Mbytes from gv$pgastat
where name in ('maximum PGA allocated','aggregate PGA target parameter','aggregate PGA auto target');


查看sga/pga等设置：
select con_id, name as Parameter, value/1024/1024 as Mbytes from V$SYSTEM_PARAMETER
where name in ('pga_aggregate_target','memory_target','memory_max_target','sga_max_size','sga_target','pga_aggregate_limit','processes')
order by name;




查看进程占用pga:
SELECT p.con_id,
p.spid,
p.pid,
s.sid,
s.serial#,
s.status,
ROUND(p.pga_alloc_mem/1024/1024) as 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
ORDER BY p.pga_alloc_mem DESC;

相关参考：

Limiting Process Size with Database Parameter PGA_AGGREGATE_LIMIT (Doc ID 1520324.1)