PGA 사용량 조회하는 방법은 일반적으로 사용하는 v$pgastat를 이용하는 방법외에 몇가지가 더 있습니다.
아래내용 참고하시기 바랍니다.
1) v$process 이용하는 방법
select inst_id,
round(sum(PGA_USED_MEM)/1024/1024) "PGA_USED(MB)",
round(sum(PGA_ALLOC_MEM)/1024/1024) "PGA_ALLOC(MB)",
round(sum(PGA_MAX_MEM )/1024/1024) "PGA_MAX(MB)"
from gv$process
group by inst_id;
INST_ID PGA_USED(MB) PGA_ALLOC(MB) PGA_MAX(MB)
---------- ------------ ------------- -----------
2 1476 2466 10758
1 181 228 233
※ colume 참고
PGA_USED_MEM : PGA memory currently used by the process
PGA_ALLOC_MEM : PGA memory currently allocated by the process <<< 실제 process가 OS로부터 할당받은 PGA영역
(including free PGA memory not yet released to the operating system by the server process)
PGA_MAX_MEM : Maximum PGA memory ever allocated by the process
2) gv$pgastat 을 이용하는 방법
select INST_ID ,NAME,round(VALUE/1024/1024) "PGA_SIZE(MB)"
from gv$pgastat
where NAME= 'total PGA allocated' ;
INST_ID NAME PGA_SIZE(MB)
---------- ------------------------------ ------------
1 total PGA allocated 227
2 total PGA allocated 2497
3) DBA_HIST_PGASTAT 이용하는 방법 (AWR정보를 수집할 경우 사용)
select INSTANCE_NUMBER,NAME,round(VALUE/1024/1024) "PGA_SIZE(MB)"
from DBA_HIST_PGASTAT
where snap_id = (select max(snap_id) from dba_hist_snapshot)
and NAME= 'total PGA allocated';
INSTANCE_NUMBER NAME PGA_SIZE(MB)
--------------- ------------------------------ ------------
1 total PGA allocated 229
2 total PGA allocated 2458
4) DBA_HIST_PGASTAT 를 이용하여 PGA 증가추이를 확인하는 방법
select p.INSTANCE_NUMBER, p.SNAP_ID, s.END_INTERVAL_TIME, round(VALUE/1024/1024) "PGA_SIZE(MB)"
from DBA_HIST_PGASTAT P , dba_hist_snapshot s
where p.SNAP_ID = s.SNAP_ID
and p.INSTANCE_NUMBER = s.INSTANCE_NUMBER
and NAME= 'total PGA allocated'
order by p.INSTANCE_NUMBER, p.snap_id ;