Oracle
[PGA] ORA-04036 관리
원샷원따봉
2022. 9. 13. 14:11
KILL SESSION for sid=(1996, 47010):
Reason = alter system kill session
Mode = KILL HARD SAFE -/-/-
Requestor = USER (orapid = 195, ospid = 18183, inst = 2)
Owner = Process: DIA0 (orapid = 21, ospid = 19193)
Result = ORA-01. PGA 조회
1.1 V$PGASTAT
SELECT * FROM V$PGASTAT;
The output of this query might look like the following:
NAME VALUE UNIT
-------------------------------------------------------- ---------- ------------
aggregate PGA target parameter 41156608 bytes
aggregate PGA auto target 21823488 bytes
global memory bound 2057216 bytes
total PGA inuse 16899072 bytes
total PGA allocated 35014656 bytes
maximum PGA allocated 136795136 bytes
total freeable PGA memory 524288 bytes
PGA memory freed back to OS 1713242112 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 2383872 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 8470528 bytes
over allocation count 291
bytes processed 2124600320 bytes
extra bytes read/written 39949312 bytes
cache hit percentage 98.15 percent
타겟값 조정
alter system set pga_aggregate_target = 3500M;
1.2
v $process 에 pga 의 할당 사용 크기 가 기록
select *
from v$process
where 1=1
order by pga_used_mem desc
;1.3
# su - grid
$ sqlplus / as sysdba
SQL> set lines 200;
set pages 200;
column name format a25;
column pname format a12;
column "MegaBytes" format a10;
set numwidth 6;
select s.sid, s.serial#,p.pid, p.spid,p.pname, sn.name, round(ss.value/(1024 *1024))||'Mb' "MegaBytes"
from v$sesstat ss, v$statname sn, v$session s, v$process p
where s.paddr = p.addr
and sn.statistic# = ss.statistic#
and s.sid = ss.sid
and sn.name in ('session pga memory' , 'session pga memory max')
and p.pname like 'DIA%'
order by ss.value
/
SID SERIAL# PID SPID PNAME NAME MegaBytes
------ ------- ------ ------------------------ ------------ ------------------------- ----------
1141 3837 12 8268 DIAG session pga memory 8Mb
1141 3837 12 8268 DIAG session pga memory max 8Mb
1996 32053 21 8285 DIA0 session pga memory 12000Mb
1996 32053 21 8285 DIA0 session pga memory max 12000Mb
1.4
DIA 0 session 을 죽 이면 프로 세 스 를 다시 시작 합 니 다.
Process termination requested for pid 19193 [source = rdbms], [info = 2] [request issued by pid: 18183, uid: 10001]
2019-06-25T10:07:16.996820+08:00
KILL SESSION for sid=(1996, 47010):
Reason = alter system kill session
Mode = KILL HARD SAFE -/-/-
Requestor = USER (orapid = 195, ospid = 18183, inst = 2)
Owner = Process: DIA0 (orapid = 21, ospid = 19193)
Result = ORA-0
1.5
os 메모리 도 풀림
# vmstat 1
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 558604 173596 10071120 0 0 81 48 8683 13364 0 0 99 0 0
0 0 0 557968 173596 10071120 0 0 145 49 8070 13185 0 0 99 0 0
3 0 0 557320 173596 10071120 0 0 81 160 8428 13220 0 0 99 0 0
1 0 0 480344 173596 10071224 0 0 97 173 10465 13905 2 1 97 0 0
2 0 0 541276 173596 10071176 0 0 81 252 21906 33637 5 1 94 0 0
4 0 0 450152 173596 10071240 0 0 81 49 16280 23034 4 1 95 0 0
2 0 0 5122416 173596 10069208 0 0 81 472 23653 26264 4 2 94 0 0
2 0 0 12127104 173596 10069512 0 0 93 493 35441 35639 5 3 92 0 0
3 0 0 12032968 173596 10069540 0 0 81 48 15058 22644 4 1 95 0 0
1 0 0 12082336 173596 10069508 0 0 97 3457 18977 27614 5 1 94 0 0
5 0 0 11993120 173596 10070012 0 0 589 48 21483 34140 5 1 93 0 0
1 0 0 12032256 173596 10070016 0 0 81 781 11349 15391 3 1 97 0 0