oratop로 세션 모니터링, pga 초과로 인한 에러

OS환경 : Oracle Linux7.2(64bit)

DB 환경 : Oracle Database 18.1.0.0

에러 : 메모리 관련 에러

PGA를 많이 사용하는 작업 도중 발생한 메세지

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
ORA-03113: end-of-file on communication channel
ORA-03114: not connected to ORACLE

테스트

서버 메모리 확인

$ cat /proc/meminfo | grep MemTotal
MemTotal:        4049052 kB

=> 서버 메모리 4G 확인

sqlplus 접속

$ sqlplus / as sysdba

샘플 테이블 및 인덱스 생성

SQL> 
CREATE TABLE MAXTEST(COLA VARCHAR2(20), COLB NUMBER, COLC NUMBER, 
COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30), 
COLG NUMBER, COLH VARCHAR2(30), COLI VARCHAR2(30));
CREATE INDEX IDX_COLB ON MAXTEST(COLB); 

데이터 삽입 시도(벌크 인서트 링크)

SQL> DECLARE
TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..14000000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=99;
   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';
   w_ins(i).COLE :='EEE  2    3    4    5    6    7    8    9   10  EEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
END LOOP;
   FORALL i in 1..1400000 INSERT INTO MAXTEST VALUES w_ins(i);
   COMMIT;
END;
/

에러 발생(ORA-04036)

DECLARE
*
ERROR at line 1:
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
ORA-06512: at line 6

pga_aggregate_limit 파라미터 값 변경(0=제한 없음)

SQL> alter system set pga_aggregate_limit = 0;

데이터 재삽입 시도(벌크 인서트 링크)

SQL> DECLARE
TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..14000000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=99;
   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';
   w_ins(i).COLE :='EEE  2    3    4    5    6    7    8    9   10  EEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
END LOOP;
   FORALL i in 1..1400000 INSERT INTO MAXTEST VALUES w_ins(i);
   COMMIT;
END;
/

에러발생(ORA-03113, ORA-03114)

ERROR:
ORA-03114: not connected to ORACLE
 
 
DECLARE
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 11068
Session ID: 84 Serial number: 32622

ORATOP로 세션 상황 모니터링

$ cd $ORACLE_HOME/suptools/oratop/ 
$ ./oratop / as sysdba
Oracle 18.0- ORA 16:24:43 up: 3.1d,  1 ins,    3 sn,   2 us,   1G sga, 73.1% db
ID %CPU %DCU LOAD   AAS  ASC  ASI  ASW  UST  IORL %FR  PGA UTPS UCPS SSRT  DBTM
 1 62.5 32.3  4.5   0.7    3    0    0    3  4.3m   4   2G    0  3.7 4.6m  731m
 
EVENT (C)                        TOT WAITS   TIME(s)  AVG_MS  PCT    WAIT_CLASS
DB CPU                                         10381           74              
resmgr:cpu quantum                   26642      2414    90.6   17     Scheduler
log file parallel write              76800       620     8.1    4    System I/O
db file async I/O submit            197044       398     2.0    3    System I/O
control file parallel write         124307       297     2.4    2    System I/O
 
ID   SID     SPID USR PROG S  PGA SQLID/BLOCKER OPN  E/T STA STE EVENT/*LA  W/T

 1    84    11068 SYS sqlp D   6G               PL/ 1.0t ACT CPU PGA memor   3u

=> 해당 세션이 PGA를 6G나 점유하고 있음을 확인가능 

=> 물리 메모리가 4G여서 위 에러 발생한 것

alert log 확인

2018-07-09T16:24:45.108288+09:00
WARNING: Heavy swapping observed on system in last 5 mins.
Heavy swapping can lead to timeouts, poor performance, and instance eviction.

해결방법 : 물리 메모리를 더 증가시켜준뒤 작업 or 한번에 하는 작업단위를 더 줄여서 pga 사용량을 줄여서 작업

물리 메모리를 더 증가시켜준뒤 작업하거나 한번에 하는 작업단위를 더 줄여서 pga 사용량을 줄여서 작업해야함

원인 : 물리메모리가 4G인데 pga_aggregate_limit 파라미터를 0으로 바꿈으로써 PGA 제한을 풀어 6G까지 사용하게 되어서 발생한 에러

물리메모리보다 pga 를 더 사용하려 해서 발생한 문제

참조 :