OS환경 : Oracle Linux 7.5 (64bit)
DB 환경 : Oracle Database 19.7.0.0
에러 : alert log 의 KILL SESSION for sid=(379, 43032): Reason = pga_aggregate_limit
물리메모리 16gb 상태에서
memory_target 12gb 설정 후
아래 구문 실행 이후 alert log 에 발생한 메세지
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> create table dummy (cola number, colb number, colc number, cold number, cole number) nologging; SQL> declare type tbl_ins is table of dummy%rowtype index by binary_integer; w_ins tbl_ins; begin for i in 1..100000000 loop w_ins(i).cola :=i; w_ins(i).colb :=10; w_ins(i).colc :=i+1; w_ins(i).cold :=i+3; w_ins(i).cole :=99; end loop; forall i in 1..100000000 insert into dummy values w_ins(i); commit; end; / |
먼저 ssh 쉘창이 매우 느리게 반응하고 새로운 ssh 세션은 붙지 못함
이후 alert log에 발생한 메세지
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | $ vi /app/oracle/diag/rdbms/orcl19/orcl19/trace/alert_orcl19.log 2020-09-27T18:43:29.456050+09:00 Errors in file /app/oracle/diag/rdbms/orcl19/orcl19/trace/orcl19_qm02_9914.trc (incident=38844): ORA-00445: background process "Q001" did not start after 120 seconds Incident details in: /app/oracle/diag/rdbms/orcl19/orcl19/incident/incdir_38844/orcl19_qm02_9914_i38844.trc 2020-09-27T18:45:52.475396+09:00 PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 12288 MB 2020-09-27T18:45:55.993145+09:00 KILL SESSION for sid=(379, 43032): Reason = pga_aggregate_limit Mode = KILL HARD SAFE -/-/- Requestor = DBRM (orapid = 14, ospid = 9612, inst = 1) Owner = Process: USER (orapid = 35, ospid = 10080) Result = ORA-31 2020-09-27T18:46:01.212053+09:00 Errors in file /app/oracle/diag/rdbms/orcl19/orcl19/trace/orcl19_ora_10080.trc (incident=38684): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT Incident details in: /app/oracle/diag/rdbms/orcl19/orcl19/incident/incdir_38684/orcl19_ora_10080_i38684.trc 2020-09-27T18:51:01.441636+09:00 Heavy swapping observed on system 2020-09-27T18:51:01.443084+09:00 WARNING: Heavy swapping observed on system in last 5 mins. Heavy swapping can lead to timeouts, poor performance, and instance eviction. |
위 메세지 발생 이후 쉘 멈춤현상이 제거됨(세션이 kill됨)
쿼리 실행중이던 세션
1 2 3 4 5 6 7 8 9 | ?declare * ERROR at line 1: ORA-00028: your session has been killed SQL> SQL> SQL> SQL> SP2-0171: HELP system not available. SQL> |
kill 되었다고 나오고 모든 내용이 롤백됨
db는 내려가지 않고 해당 세션만 kill됨
새로운 세션을 붙으려고 할 때 발생하는 메세지
1 2 3 | # su - oracle Last login: Sun Sep 27 18:28:49 KST 2020 on pts/0 'abrt-cli status' timed out |
해결 방법 : 적정한 양으로 작업을 나누어서 실행
적정한 양으로 작업을 나누어서 실행
원인 : 과도한 시스템 사용
과도한 시스템 사용
참조 :