[PGA] ORA-04036 관리

 

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. 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