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

회사에 도착 후 서버 점검을 하는데 오라클 ORA-04031 에러로 인해 개발서버가 실행되지 않고 있었습니다.

이 에러 내용은 nn byte에 공유 메모리를 할당할 수 없다는 내용으로 shared memory를 필요로 하지만 적재를 하지 못해 나는 에러입니다.

발생원인

ORA-04031 에러는 Shared Pool의 관리가 원활히 되지 않아 수행되는 SQL이 적재할 Memory가 없어 error가 발생해서 생기는 게 원인입니다.

구체적인 에러 원인으로 크게 2가지가 있습니다.

• Shared Pool Fragmentation (Shared Pool 과도한 조각화)
• Too many pinned packages (고정된 패키지가 많음)

해결방안

위와 같은 방식으로 오라클을 내렸다 올리면 바로 해결이 가능합니다.

하지만 구체적인 에러 원인에 대해 해결을 해야 에러가 반복되지 않을 것이라 생각하기에 아래와 같은 작업을 하였습니다.

1. Shared Pool Fragmentation (Shared Pool 과도한 조각화)

- 과도한 조각화를 방지하기 위해서는 Literal SQL > Bind Variable SQL로 바꿔주는 작업이 가장 좋은 방법입니다.

 하드코딩으로 박힌 SQL은 각각 마다 Shared Pool을 잡아 먹습니다. 하지만 Bind Variable SQL은 한 번 Shared Pool에 올라가서 Bind만 되는 것이기 때문에 하나의 Shared Pool만 잡아먹기 때문에 Bind Variable SQL로 대부분 바꿔주는 것이 좋습니다.

 

2. Too many pinned packages (고정된 패키지가 많음)

dbms_shared_pool.keep을 통해 크기가 큰 sql은 패키지를 해두어 빠르게 사용할 수 있도록 해둡니다.

하지만 너무 많은 keep이 일어나게 되면 새 작업을 위한 shared pool 부족 현상으로 ORA-04031의 에러가 발생할 수 있습니다. keep 해둔 것에 대한 unkeep을 통해 조절해야 될 것입니다.

 

또한 이 외에 예방 방법으로는

shared_pool_size , shared_pool_reserved_size에 RAM을 추가하거나

11g 이상에서는 memory_max_size를 늘려주는 방법이 있습니다.

 

memory_max_size를 늘리는 방법은 아래 URL에서 확인 가능합니다.

http://haisins.epac.to/wordpress/?p=354(출처:DB의 정석)