안녕하세요. 전 오윤호 입니다.

원인: shared pool에 할당된 공유 메모리 보다 더 큰 수치의 공유메모리가 필요합니다.

대처: shared pool메모리 부족의 경우, 큰 팩키지를 확보하기 위하여 DBMS_SHARED_POOL 

팩키지를 사용하는지, 사용하고 있는 공유메모리를 삭제 하든지, 

또는SHARED_POOL_RESERVED_SIZE및SHARED_POOL_SIZE 초기화 파라메타를 늘려 줌으로서, 

사용가능한 메모리 양을 증가 하여 주셔야 겠네요.

LARGE_POOL이 메모리 부족인 경우는LARGE_POOL_SIZE 초기 파라메타를 늘려 주심 되겠네요.

원본 : DBGUIDE 김형일님의 "오라클 문제해결" 커뮤니티

ORA-4031의 솔루션은 그 원인에 따라 다양한 방법이 있습니다.

먼저 ORA-4031가 발생하는 원인은, SHARED_POOL을 관리하는 과정에, 많은 조각화(Fragment)가 발생하고 Free Memory가 아주 적은 상태에서, 커다란 SQL(PL/SQL)이 Memory로 Load 될 때 공간이 부족해서 발생할 수 있습니다.

이 ora-4031 Error가 발생하게 되면, Shared pool의 관리가 원활히 되지

않아, 이후에 수행되는 모든 SQL이 error가 발생합니다. 그러므로 이는

반드시 예방되어야 합니다.

 이러한 Memory관리상의 문제를 해결하기 위해 조치 할 수 있는 것은 아래의 것들이 있습니다.

1.      v$sql 내의 Literal SQL이 많은지 확인한다.

많은 경우 Literal SQL을 사용하는 SQL을    찾아서 공유 할수 있도록 Bind Variable을 사용토록 하면 됩니다.

   => Literal SQL을 찾는 방법.

select substr(sql_text, 1, 40) "SQL",

       count(*) cnt,

       sum(executions) "TotExecs",

       sum(sharable_mem) mem,

       min(first_load_time) start_time,

       max(first_load_time) end_time,

       max(hash_value) hash        

from v$sqlarea

where executions < 5    --> 수행 횟수가 5번 이하인 것.

group by substr(sql_text, 1, 40)

having count(*) > 30    --> 비슷한 문장이 30개 이상.

order by 2 desc;

2. v$sql 내의 sharable Memory가 큰것들을 확인 한다.

   1M byte이상의 SQL이 있다면 확인 후 SQL의 복잡도를 줄인다(recursive call을 많이 한다든지..). 대부분의 경우 크기가 큰 것들은 일반 SQL이 아니라 PL/SQL이므로 이러한 것들은 Memory에서 내려오지 않도록 Pin을 시키는 방법도 있습니다. (그렇다고 memory에서 완전히 안내려 오는 것은 아닙니다.)

   => PL/SQL을 Memory에 Pin시키는 방법.

   execute dbms_shared_pool.keep('SCOTT.HELLO_WORLD'); 

3. SHARED_POOL_SIZE와 SHARED_POOL_RESERVED_SIZE의 크기를 늘린다.

   항상 Shared pool의 Free가 여유가 있도록 shared_pool_size를 크기를 좀 늘리시고

   특히 Shared_pool_reserved_size의 크기를 100M정도 되도록 지정하세요. 경험적으로 shared_pool_reserved_size가 100M정도 지정하면 ora-4031가 많이 발생하지는 않더군요.

   Free공간 확인 .

   SELECT free_space, avg_free_size, used_space,

          avg_used_size, request_failures, last_failure_size

   FROM    v$shared_pool_reserved;

4. 이것이 진짜 마약처럼 잘 듯는 방법인데, 9i부터는 Shared_pool의 관리를 좀더 효율적으로 하고 System의    CPU를 효과적으로 사용하기 위해 하나의 heap memory를 사용하던 것을 subheap으로 나누어 관리를 하고 있지요. 이렇게 sub-heap으로 나누어 관리하다 보니 작은 공간이 sub heap에 동시에 있더라도 이를 잘 활용하지 못해서 발생하는 경우가 있습니다. 이러한 이유로 ORA-4031 Error의 원인이 되는 경우가 종종 있습니다.

   현재 시스템이 Multi CPU인 경우에는 아마도 1보다 큰 값으로 되어 있을 겁니다.

   그래서 아래의 Query로 조회해 본 후 그 값이 1보다 큰 값이라면 init.ora에서

   _kghdsidx_count=1로 지정한 후 restart해서 사용해 보세요. 어지간해서 ORA-4031가발생 하지 않을 겁니다.

            select x.ksppinm, y.ksppstvl

             from x$ksppi x , x$ksppcv y

             where x.indx = y.indx

             and x.ksppinm like '_kghdsidx_count%' escape ''

             order by x.ksppinm;

 오래된 내용이긴 하지만...  위 한은정님 내용 중에 잘못된 부분을 지적하기 위해 글을 씁니다.

3번 SHARED_POOL_RESERVED_SIZE는 기본적으로 SHARED_POOL_SIZE의 5%를 할당합니다.

100M면 요즘 기준으로 말도 안되는 사이즈입니다.

만약 기본값인 5%에서 증가시키길 원하면 _shared_pool_reserved_pct=10 (10%)으로 설정하길 바랍니다.

4번 _kghdsidx_count=1이 만병통치약처럼 적어놨는데...

subpool을 이전처럼 1개로 합치는 파라미터입니다.  경우에 따라 ORA-4031을 줄일 수 있을지 몰라도 래치 경합을 증가시키는 방법입니다. 혹시 User calls이 높은 사이트일 경우 "1"은 절대 권하지 않습니다.

P.S:

ORA-4031의 원인은 Literal SQL, 잘못 설정된 shared pool size  외에도 오라클 버그에 기인하는 경우가 많습니다.

반드시 전문 엔지니어 분석을 통해 조치를 받으시고 인터넷에서 검색한 내용을 함부로 적용하지 마시기 바랍니다.