[Error] ORA-4031 shared memory 부족 현상 오라클

ORA-04031: unable to allocate xxx bytes of shared memory 

오라클에서 발생하는 ORA Error 중 하나인 ora-4031는 여러 가지 이슈들로 인해 발생되는 대표적인 케이스입니다.

ORA-04031 은 ORA-600과 더불어 여러 가지 다양한 케이스들이 존재 하지만 오늘은 몇 가지 사례를 들어가며 대표적으로 어떤 것들이 있는지 알아보도록 하겠습니다.

1. ORA-4031 발생 원인

발생 원인은 주로 Shared pool의 사용 가능한 메모리가 작은 조각 단위로 분할되는 메모리 단편화가 발생하게 되며, 시간이 흐를수록 메모리 단편화가 더욱 심해져 큰 메모리를 할당해야 하는 작업이 발생하게 된다면 Shared Memory가 부족하다는 ORA-4031가 발생하게 됩니다.

다시 정리해서 말하면, 전체적으로 많은 양의 메모리 공간이 있다고 하여도 충분한 양의 연속적인 공간이 없다면 메모리가 부족하다는 Error 가 발생하게 됩니다.

2. ORA-4031 조치방법

원인을 확인했으니 이제 조치방법을 통해 장애를 해결해야 할 차례입니다. 연속된 메모리 공간이 부족하니 메모리를 확보해주면 됩니다. 메모리를 확보하는 방법은 아래와 같이 여러 가지가 있습니다.

1. 오래된 session을 강제로 kill 해서 shared pool의 메모리 공간을 확보합니다. (한두 개 session kill 해서 해결 안 됩니다)
2. alter를 이용한 메모리 초기화 alter system flush shared_pool (운영 중인 환경에서 안 하는 게 나음 차라리 재기동을...)
3. DB 재기동

하지만 위의 방법들은 완벽한 해결책이 아닙니다. 특히 ORA-4031가 자주 발생하는 경우라면 session을 kill 하거나 DB 재기동을 해봐야 얼마 가지 못해 또 메모리 단편화가 발생될 것이 때문입니다.  일단 급한 불을 위의 방법대로 해결을 했다면 이제 근본적인 원인을 찾아 제거해야 합니다. 

그중에서 가장 근본적인 방법은

1. v$sql 내의 리터럴 SQL이 많은 이 확인해야 합니다.(리터럴 SQL 찾는 SQL 문은 여기 ) 대부분은 리터럴 SQL만 많이 잡아내서 bind 화 시킨다면 많은 개선이 되지만 여기서 조금 더 욕심을 내자면
2. shareable memory가 큰 SQL을 V$sql에서 검색하여 찾아냅니다. 그 후 SQL 튜닝을 해서 복잡도를 낮추거나 메모리에서 내려오지 못하도록 pin 시키는 방법도 있습니다.

여기까지 Oracle DB 사용 시 발생할 수 있는 ORA-4031에 대해 원인부터 해결방안까지 알아보았습니다.

참고로, 과도한 메모리 단편화로 인해 sqlplus / as sysdba로 나조차도 접속이 안될 때 2가지 방법이 있습니다.

1. DB에 접속된 session 강제 kill

oracle> ps -ef | grep LOCAL=NO
..
..
session의 pid 확인
oracle> kill -9 xxxx

위 방법은 현재 DB에 접속된 session 중 무작위 하게 kill 시키는 방법이라 최후에 쓰시는 걸 추천드립니다.

2. sqlplus 강제 접속

oracle> sqlplus -prelim / as sysdba

위 명령을 통해 sqlplus를 접속하시면 되지만 기본적인 명령어들은 수행되지 않습니다.

수행되는 명령어는 oradebug 나 shutdown 정도만 가능합니다.

 

리터럴 SQL 확인 방법

select   sql_id         "SQL ID"
        ,replace(replace(replace(sql_text,chr(9),' '),chr(10),' '),chr(13),' ') "SQL Text"
    ,cnt        "Count"
    ,executions     "Exec (Max)"
    ,sharable_mem   "Memory (bytes)"
    ,module     "Module"
    ,hash_value "Hash_value"
from    (
    select   substr(sql_text,1,60) sql_partial
            ,max(to_char(substr(sql_fulltext,1,2500)))  sql_text
        ,count(*) cnt
        ,sum(sharable_mem) sharable_mem
        ,max(hash_value) hash_value
        ,max(sql_id) sql_id
        ,max(executions) executions
        ,max(module) module
    from v$sql
    where executions > 0
      and executions < 5
    group by substr(sql_text,1,60)
    having count(*) >= 10
    order by cnt desc, sharable_mem desc
    )
where rownum <= 123;