[오라클] ORA-04031 에러 원인 및 해결방안

 

오라클을 사용하다 보면 마주할 수 있는 에러입니다.

흔히 알고 있는 방안으로 DB Instance를 재기동 하는 방법이 있지만

에러의 원인 및 해결방안에 대해 더 자세히 알아보도록 하겠습니다.

 

 

 

 

 

 

 

 

 

에러 발생 원인

ORA-04031 에러는 unable to allocate shared memory라는 문구와 같이 나옵니다.

내용을 간단히 해석해보면 공유 메모리(shared memory)를 할당할 수 없다는 뜻으로 공유 메모리의 부족.

즉, 메모리 단편화(Fragmentation)에 따라 연속된 Parsing공간을 제공하지 못하여 발생하는 현상입니다.

 

 

에러의 원인은 크게 두가지가 있습니다.

 

1. Shared Pool의 단편화(Fragmentation)로 인해 Pool size가 작아서 발생합니다.

Shared Pool은 공유풀로 SGA를 관리하는 메커니즘, 파라미터(Parameter)정보, 실행된 SQL, SQL 분석/실행 정보 및 오라클 오브젝트 등의 정보를 저장하는 메모리 공간입니다. Shared pool은 cache와 같은 기능을 하기도 하며, 크기가 크거나 메모리 사용량이 많은 SQL을 Keep 해두고 있다가 필요할 때, 바로바로 사용할 수 있도록 해줍니다.

 

Shared pool에 있으면 Soft parsing이 일어나고, Shared pool에 없으면 Hard parsing이 일어납니다.

 

리터럴 SQL이 많이 수행되면 cost가 높기 때문에 hard parsing이 많이 일어나고 그에 따라 shared memory가 부족해져서 생기게 됩니다.

 

 

2. Memory는 있으나 PL / SQL Packgae가 너무 많은 경우

크기가 큰 SQL의 경우 계속 호출을 하게 되면 hard parsing이 일어나 메모리에 부하를 주게 되는데요, 그래서 shared pool에 keep해놓고 사용하게 됩니다. 하지만, shared pool에 keep을 해놓는 SQL Package가 많은 경우 새 작업을 위한 shared pool의 용량이 부족하게 되어 ORA-04031에러가 발생할 수 있습니다.

 

 

 

 

 

 

 

 

 

 

 

에러 해결 방안

해당 에러의 해결 방안에도 몇가지 방안이 있습니다.

 

 

1. DB Instance 재기동

간단한 방법으로 DB Instance를 재기동 하는 방법이 있습니다. DB를 재기동하게되면 할당되어있는 메모리가 flush되어 할당 가능한 메모리 및 세션이 생기게 됩니다.

 

2. DB 세션 Kill

오랫동안 수행되는 SQL을 확인하고 해당 세션을 kill해주는 방법이 있습니다. Plan을 잘못 타거나, 조회범위를 넓게 하여 무거운 SQL이 수행되었을 경우 메모리 및 세션을 많이 차지하는 경우가 있습니다. 그 경우 해당 SQL의 세션을 kill 해주면 에러 발생은 하지 않게 됩니다. 근데 해당 SQL이 계속 들어올 수 있으므로 근본원인을 찾아 개선하는 것이 필요합니다.

 

3. 리터럴 SQL수정

리터럴 SQL은 Dynamic SQL이라고 불리며 Bind변수를 사용하지 않아 조건 값에 상수가 변경되어 수행되는 SQL입니다. 같은 형태의 SQL이라도 상수가 변경될 경우 서로 다른 SQL로 인식하게 되어 수행 횟수만큼 hard parsing이 일어나게 되고 Shared pool의 공간을 많이 차지하게 됩니다. 그래서 리터럴 SQL을 Bind SQL로 변경하면 Hard parsing을 줄이고 메모리 관리를 효율적으로 할 수 있습니다.

 

4. Shared Pool 사이즈 늘리기

Shared Pool의 사이즈를 조절할 수 있는 권한이 있다면 alter system set shared_pool_size "사이즈" 명령어를 통해 shared pool의 사이즈를 조절해 줄 수 있습니다. Shared pool의 사이즈 조절을 통해 에러 발생을 줄일 수 있습니다.

 

 

 

이상으로 ORA-04031 unable to allocate shared memory 의 발생 원인 및 해결방안에 대해 알아보았습니다.

 

도움이 되셨길 바랍니다. 감사합니다.