뉴로의 탈출게임

 

RA-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;
            
출처: http://database.sarang.net/?inc=read&aid=22794&criteria=oracle&subcrit=&id=&limit=20&keyword=ORA-04031&page=1
참고: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1513005503967

 

===================================================================