database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 22794 게시물 읽기
No. 22794
ora-04031오류입니다...!!
작성자
이윤호(icbmssfe)
작성일
2005-05-26 15:21
조회수
6,007

고수님들 좀 봐주세요..

 

-1

ORA-04031:unable to allocate 19204 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","session param values")

 

라고 에러코드가 뜹니다..

 

한달에 한번정도...나타나는데요 ..서버 제 부팅을 하면 괜찮아 지는데....제대로 관리를 해보고 싶어서요...!!

 

상세히좀 알려주세요~^^;; 제가 잘 몰라서요..부탁드립니다.

 

이 글에 대한 댓글이 총 3건 있습니다.

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

원인: shared pool 할당된 공유 메모리 보다   수치의 공유메모리가 필요합니다.
대처: shared pool메모리 부족의 경우,  팩키지를 확보하기 위하여 DBMS_SHARED_POOL 
팩키지를 사용하는지, 사용하고 있는 공유메모리를 삭제 하든지, 
또는SHARED_POOL_RESERVED_SIZESHARED_POOL_SIZE 초기화 파라메타를 늘려 줌으로서, 
사용가능한 메모리 양을 증가 하여 주셔야 겠네요.
LARGE_POOL이 메모리 부족인 경우는LARGE_POOL_SIZE 초기 파라메타를 늘려 주심 되겠네요.
ohyouknow님이 2005-05-26 15:33에 작성한 댓글입니다.
이 댓글은 2005-05-26 15:38에 마지막으로 수정되었습니다. Edit

원본 : 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에 작성한 댓글입니다. Edit

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

 

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  외에도 오라클 버그에 기인하는 경우가 많습니다.

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

 

오라클전문가님이 2020-02-03 21:12에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
22799쿼리 한번 봐주세요~~~!!!!! [1]
제발제발..
2005-05-26
1422
2279810g 서버 설치후, 랜연결만 하면 서버 Connection Error [1]
이문정
2005-05-26
1620
22796콘트롤 파일, REDO LOG파일 문의 [5]
임영택
2005-05-26
3672
22794ora-04031오류입니다...!! [3]
이윤호
2005-05-26
6007
22793ora-28546 connection to server failed, probable Net8 admin error
이문정
2005-05-26
2367
22792sqlnet.ora 의 디렉토리 경로? [2]
이중희
2005-05-26
2344
22791데이터 베이스가 오픈이 안돼요.. [1]
이수연
2005-05-26
1492
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2019 DSN, All rights reserved.
작업시간: 0.265초, 이곳 서비스는
	PostgreSQL v11.5로 자료를 관리합니다