[Oracle] ORA-04031 에러 해결 방법

ORA-04031 에러는 Shared Pool 내에 메모리가 단편화됨에 따라 연속된 parsing 공간을 제공하지 못해 발생하는 에러이다.

메모리가 단편화되는 이유는 리터럴 SQL을 많이 사용하는 경우에 그럴 수 있는데, 해결 방안으로는 리터럴 SQL을 줄이거나 Shared Pool size를 늘리는 방안이 있다.

 

나는 시스템 운영중에 ORA-04031 에러가 발생하였고 해결 방법을 찾아 다녔는데 ORA-04031 에러가 아무리 발생시키려고 해도 발생하지가 않았다. 그래서 확인해보니 에러가 난 시간에 Shared Pool이 자동 조정되어 반영이 되어 그 뒤에 똑 같은 에러가 발생하지 않은것이다.

 

에러 원인을 파악하고 해결하기 위한 방안 3가지 방법

1. 오래된 세션을 끊어서 shared pool에 잡고 있는 메모리를 해제한다.

 -> 확인 방법은 오랫동안 잡고있는 DB 세션을 확인하고 끊어주는 것인데 오랫동안 잡고 있는 세션으로 인해서 에러가 발생한 것은 아니였다.

2. 리터럴 SQL을 수정한다.

 -> 리터럴 SQL을 수정하는 방법도 ORA-04031을 해결하는 방법인데 쿼리로 리터럴 SQL을 체크할 수 있었다. 하지만 리터럴 SQL이 차지하는 비율이 30% 정도 밖에 안됐고 이때문에 에러가 발생한것 같진 않았다.

3. Shared Pool의 사이즈를 늘린다.

-> 가장 단순한 방법인데 권한 문제로 사이즈를 늘릴수가 없었다. 하지만 alter system flush shared_pool 명령어를 통해 shared pool 의 단편화를 다시 flush 해줄수는 있다.

 

마지막으로  shared pool 사이즈를 확인 하려고 v$sga_dynamic_components 테이블을 조회해봤는데 

 v$sga_dynamic_components 테이블에서 조회해보면, 어제 아래의 에러가 난 시간에 Shared Pool이 조정되었다는 것을 확인할 수 있다. 그래서 Shared Pool 사이즈가 자동으로 늘어나 에러가 해결됬다는것을 확인할 수 있었다.

 

참고

https://jack-of-all-trades.tistory.com/72