오라클 에러 정보 (ORA-04031) - Shared Pool 공간이 부족한

오라클이 사용하는 메모리 영역을 SGA(System Global Area) 라고 하고, 이 SGA 내에서는 용도에 따라 여러 영역으로 구분됩니다. 이 영역중에서 SQL 또는 PL/SQL 의 SQL Statement 및 파싱정보를 보관하고 있는 공간을 Shared Pool 이라고 합니다.

더 정확하게 얘기하면 Shared Pool 안에서 또 나눠집니다.

          1) SQL 또는 PL/SQL 의 SQL Statement 및 파싱정보를 저장하는 Library Cache 영역

          2) 오라클 딕셔너리(Dictionary) 정보를 저장하는 Row Cache 영역

 

(출처 : Oracle Database Administrators Guide)

SQL 문장과 파싱정보를 뭐하러 Shared Pool 에 보관할까요? 그건 매번 똑같은 SQL문을 실행할 때마다 이 SQL을 어떻게 실행할 지 Optimizer가 Plan 을 만들고, Dictionary Table 들을 뒤지고 하는 작업이 매우 반복적이며 소모적이기 때문입니다. 이 시간을 줄이려고 하는 노력이죠.

 

이렇게 Shared Pool 에 정보가 이미 들어있어서 SQL 실행할 때 다시 파싱하지 않아도 되는 경우를 Soft Parsing 이라고 하고, 이게 없어서 처음부터 정보를 다시 수집하고 Plan(실행계획)을 다시 만들고 하는 경우를 Hard Parsing 이라고 합니다.

 

한번 Hard Parsing 을 했다면, 다시 또 이 짓을 하지 않기위해 Shared Pool 에 정보를 저장해두는게 좋겠죠?

 

ORA-04031 에러는 이 Shared Pool 공간이 부족해서 애써 파싱한 정보를 저장할 수 없을때 발생합니다.

그러면, 단순히 생각해서 Memory 를 늘려주면 되겠죠? 그중에서도 특히 shared_pool_size 라는 파라메타를 늘려주면 됩니다.

 

SQL> alter system set shared_pool_size = 200M scope=both;

 

이게 줄이는건 잘 되는데, 늘릴때는 ORA-02097, ORA-04033 에러를 만나는 경우도 있습니다.

그런 경우는 위에서 scope=both 를 scope=spfile 로 해서 먼저 바꾼 후, 재기동하면 됩니다.

또, 고려할 부분이 sga_target 이나 memory_target 을 쓰고 있다면 당연히 이 범위내에서만 조정이 가능하겠죠?

 

그리고, sga_target 이나 memory_target 을 세팅했더라도 shared_pool_size 값을 함께 잡아주는 것이 좋습니다.

sga_target, memory_target 을 세팅하면 AMM(Automatic Memory Management) 라고해서 오라클이 자동으로 메모리 관리를 하는 기능인데, 이게 항상 잘 작동하지는 않는 것 같습니다. 어떤 경우는 Shared Pool, Large Pool 등을 작게 잡았다가 필요시 늘리면서 성능이슈도 생기고 한다고 합니다.

하지만, shared_pool_size, large_pool_size 값을 같이 잡아주면 AMM 기능을 사용해서 자동관리를 하긴 하지만, 적어도 사용자가 명시한 shared_pool_size, large_pool_size 값은 처음에 잡고 올라와서 보다 더 안정적으로 동작한다고 합니다.

 

shared_pool_size 를 늘려줘도 ORA-04031 에러가 계속 발생하는 경우도 있습니다.

이때는 다른 부분도 확인해 봐야 합니다.

가장 많이 언급되는 부분이 Literal SQL 이라는 놈입니다. (Dynamic SQL 이라고도 합니다)

 

SQL> select *
     from EMP
     where ename = 'SMITH';

SQL> select *
     from EMP
     where ename = 'JONES';

 

위 두 SQL 은 우리 눈으로 보기에는 같죠. ename 값만 바뀔 뿐 똑같은 SQL로 보이므로, 파싱정보도 하나만 있으면 될 것 같지만, 오라클 입장에서는 한 글자만 달라도 다른 SQL입니다. ㅎㅎ

따라서 저런식으로 값만 바뀌면서 한 100000 번 실행한다면, 100000 개의 파싱정보를 Shared Pool 에 저장할려고 할 겁니다. 물론 Oracle DBMS 가 정말 바보는 아니라 100000 개를 하나도 안지우고 메모리에 다 쌓지는 않습니다. LRU 알고리즘에 의해서 오래된 것은 또 밀어내면서 공간을 확보합니다. 하지만, 다 밀어내기도 전에 자꾸 SQL들이 실행되어야 하는 상황이 생길 수 있고, ORA-04031 에러를 만날 확률이 늘어나는 것 입니다.

 

고로, Literal SQL 을 줄이는 것이 좋습니다.

바꾸는 방법은 변수를 사용하면 됩니다. 이를 Dynamic SQL 에 대비해서 Static SQL 이라고 합니다.

 

SQL> select *
     from EMP
     where ename = :AA1;

 

AA1 이라는 변수에 값만 넣으면 되겠죠. 그러면, 오라클은 AA1 의 변수값이 아무리 바뀌어도 모두 같은 SQL로 보기때문에 한개의 파싱정보만 Shared Pool 에 저장하면 됩니다. 메모리를 효과적으로 사용하게 되는 것이지요.

 

 

이 외에도 더 고려할 수 있는 방법으로 아래의 2가지가 더 있습니다.

 

   1) Shared Pool 공간에 자주쓰는 큰 SQL 또는 PL/SQL을 Keep 해 두는 방법

        Shared Pool 영역도 썼다 지웠다를 반복하기 때문에 free 영역이 일정하게 모여 있는게 아니고
        여기저기 작은 조각으로 분산되어 있게 됩니다. 전체를 모아보면 많이 남아있지만, 큰 free 조각이 없어서
        큰 SQL 이나 PL/SQL 을 올릴 수 없는 경우가 있을 수 있습니다.

        이때 자주 사용하는 큰 놈들을 Keep 해두는 것이죠.. dbms_shared_pool.keep() 을 사용합니다.
        하지만, Keep 안해놓은 놈들은 에러가 나도 되는건가?? 이건 쫌~~ 이럴거면 shared pool 을 늘려주는게 ~~

 

   2) 히든파라메타 _kghdsidx_count = 1 로 세팅하는 방법

        Shared Pool 영역은 관리를 효율적을 하기 위해 DB 서버의 CPU 가 많은 경우는 여러개의 Sub Pool 로 나누어서
        관리를 합니다. 이 Sub Pool 을 안쓰겠다는 겁니다. 이 방법으로 효과를 봤다는 데가 종종 있기는 합니다.

        하지만, 항상 그렇듯이 히든파라메타의 변경은 오라클의 가이드를 받는게 좋습니다. Just in case ~

 

 

위에서 언급한 모든 조치를 했음에도 불구하고 여전히 ORA-04031 에러를 만나는 경우가 있습니다.

그때는 버그상황을 의심해봐야겠죠. ORA-04031 관련해서 MOS(구 메타링크)에서 찾아보면 매우 많은 문서들을 찾아볼 수 있습니다. 버그도 꽤 있습니다. 따라서 항상 최신 패치셋을 유지하는 것이 좋겠습니다.