본문 바로가기

DB/oracle

ORA-4031 장애 해결

반응형

원본 : 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;

한은정님이 2005-05-26 16:46:59에 작성한 댓글입니다.

출처 : http://database.sarang.net/?inc=read&aid=22794&criteria=oracle&subcrit=&id=&limit=20&keyword=ORA-12541&page=1
반응형

'DB > oracle' 카테고리의 다른 글

오라클 alert log 경로 보기  (0) 2011.06.08
테이블 컬럼 순서 조절하기  (0) 2011.03.25
SUBSTR  (0) 2011.01.05
기존 테이블 백업용 쿼리  (0) 2010.08.30
오라클 세션 검색  (0) 2010.07.19
댓글