[DB]오라클 ORA-04031 Error

ORA-04031

회사에서 정말 많이 났던 ORA-04031 에러이다. 지금도 회사에서 간혹 나고 있는데, 현재 조치는 DB Instance를 재기동 하는 방법으로 해결하고 있다.

(여러 조치를 취했지만 해결하지 못해 Instance 재기동으로 진행중이다..😂)

오라클 SR로 ORA-04031에 대해 알아보자.

ORA-04031 왜 발생하는지?

ORA-04031은 Shared Pool Memory Fragmetation으로 인해 발생되는 ORA 메시지이다. DB에 가장 치명적인 오류라 할 수 있다.

ORA-04031은 아래 사항으로 오류가 발생한다.

1. Shared pool Memory 부족
   • SQL의 hard parsing이 매우 많이 일어나서 Shared memory가 부족해짐

오라클 Shared Pool

• 라이브러리 캐쉬 - 데이터베이스에 접속한 유저가 실행한 SQL, 오라클이 내부적으로 사용하는 SQL(Recursive SQL), SQL에 대한 분석 정보(Parse Tree) 및 실행 계획(Execution Plan)이 저장된다.

• 데이터 딕셔너리 캐쉬 - 데이터 딕셔너리 캐쉬는 로우 캐쉬(Row Cache)라고도 부른다. 데이터 딕셔너리 캐쉬에는 테이블, 인덱스, 함수 및 트리거 등 오라클 오브젝트 정보 및 권한 등의 정보가 저장된다.

• Shared Pool(공유 풀) - SGA를 관리하는 메커니즘, 파라메터 정보, 실행된 SQL, SQL 분석/실행 정보 및 오라클 오브젝트 정보를 저정하는 메모리 공간

Shared pool은 SQL을 분석 실행하는데 하드파싱한 SQL을 저장 관리한다.

• SGA 메모리 확인

  -- SGA 메모리 확인 (SQLPLUS)
  show paramter sga_max_size;
  -- SGA 메모리 변경
  alter system set shared_pool_size = 10G;
  

Hard Parsing

하드파싱은 SQL을 처리하는 오라클의 방식은 아래와 같다.

1. SQL을 실행하면 제일먼저 SQL 파서(parser)가 SQL 문장에 문법적 오류가 없는지 검사(Syntax 검사)
2. 문법적 오류가 없다면 의미상 오류가 없는지 검사(Semantic 검사, 오브젝트 존재유무등)
3. 검사를 다 마치면, 사용자가 발생한 SQL과 그 실행계획이 라이브러리캐시(프로시저캐시)에 캐싱되어 있는지 확인
4. 캐싱되어 있다면 소프트파싱, 캐싱되어있지 않다면 하드파싱

ORA-04031의 해결방법

ASSM관리

오라클 10g에서 처음 등장한 Shard pool size 자동 관리해주는 옵션이다.

alter system set sga_max_size=10240m scope=spfile;
alter system set sga_target=10240m scope=spfile;

ASMM으로 관리되는 SGA 구성요소에 대하여 아래 쿼리를 이용하여 관찰할 수 있다.

select component, current_size/1024/1024 curr_MB, min_size/1024/1024 min_MB,
user_specified_size/1024/1024 user_MB
from v$sga_dynamic_components;

Hard Parsing을 줄이자

아래 구문은 hard parsing이 많이 일어나는 sql을 조회하는 쿼리

SELECT substr(sql_text,1,40) "SQL",
count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2;

위 쿼리를 통해서 하드파싱의 쿼리를 찾아내서 아래와 같이 하드파싱을 줄여야 한다.

• 바인드 변수 사용하기 바인드 변수는 아래와 같이 사용하면된다. 간단한 구문이라 설명하기도 민망하다.

:LOGIN_ID라는 바인드 변수를 통해서 SQL의 재사용성을 높인다.

SELECT * FROM CUSTOMER WHERE LOGIN_ID = :LOGIN_ID; 

위 SQL은 사실 어플리케이션 단 개발자가 신경써야 되는 부분이라서 DBMS의 안정성을 위해서라도 개발자들의 SQL문장에 대한 교육이 필요하다.

참조

ASMM, AMM 메모리 관리기법 SQL 파싱 부하