조회 수 12661 추천 수 0 댓글 4

수시로 ORA-04031 에러가 발생합니다.  일반적으로 가장 많이 발생하는 원인과 근본적인 조치방법 임시 조치방법등등  

실무적으로 어떻게 대응해야 하는지 궁금합니다.

  • 관리자 2011.05.17 21:52

    ORA-04031

    (1) 에러 메시지

    [ora11@localhost ~]$ oerr ora 4031

    04031, 00000, "unable to allocate %s bytes of shared memory ("%s","%s","%s","%s")"

    // *Cause: More shared memory is needed than was allocated in the shared pool.

    // *Action: If the shared pool is out of memory, either use the

    // DBMS_SHARED_POOL package to pin large packages,

    // reduce your use of shared memory, or increase the amount of

    // available shared memory by increasing the value of the

    // initialization parameters SHARED_POOL_RESERVED_SIZE and

    // SHARED_POOL_SIZE.

    // If the large pool is out of memory, increase the initialization

    // parameter LARGE_POOL_SIZE.

    (2) 원인

    새로운 SQL에 대해서 파스를 수행하려면 힙 영역에 새로운 빈 공간을 할당해야 하며 빈 공간을 할당하기 위해서는 프리 리스트로부터 필요한 크기의 프리 청크를 찾아야 한다. 그런데 프리 리스트를 검색하고도 프리 청크를 찾지 못한다면 Shared pool LRU 리스트로부터 사용 가능한 청크를 찾아서 사용하게 된다.

    만약 LRU 리스트에서도 필요한 크기의 사용 가능한 청크를 찾지 못하면 ORA-04031 에러를 발생시키고 SQL 파스는 실패한다.

    예) 새로운 SQL 파스를 위해서 256바이트의 공간이 필요하다고 가정

    ① Shared pool 래치를 획득하고 프리 리스트로부터 256바이트의 프리 청크를 검색한다. 이 과정에서 래치를 획득하지 못하면 latch: shared pool 대기 이벤트를 발생시키며 획득 가능할 때까지 대기한다.

    ② 256바이트의 프리 청크를 찾았다면 해당 청크를 익스텐트에 할당한다. 만약 프리 리스트에 256바이트 크기의 프리 청크가 없어서 찾지 못했다면 더 큰 크기의 프리 청크를 검색한다.

    ③ 만약 256바이트보다 더 큰 400바이트의 프리 청크를 찾았다면 400바이트 프리 청크를 필요한 크기의 256바이트와 나머지 144바이트 크기로 쪼갠다.

    ④ 필요한 크기로 쪼개진 256바이트 청크는 익스텐트에 할당하고 나머지 144바이트는 다시 프리 리스트에 등록되어서 관리된다.

    ⑤ 2번 단계에서 모든 프리 리스트를 검색하고도 256바이트보다 큰 프리 청크를 찾지 못했다면 Shared pool LRU 리스트로부터 핀이 해제된(재사용 가능한) 청크 중에서 256바이트 이상의 크기를 갖는 청크를 찾아서 프리 리스트로 등록하고 3번 단계부터 진행한다.

    ⑥ 5번 단계에서 Shared pool LRU 리스트를 검색하고도 필요한 크기의 청크를 찾지 못하면 "ORA-4031 unable to allocate %s bytes of shared memory" 에러를 발생시키고 SQL 파스는 실패한다.

    프리 리스트로부터 프리 청크를 검색하고 할당받기까지의 모든 단계에서 shared pool 래치를 획득해야 한다. 그런데 청크 할당과 해제가 빈번하게 반복되면 청크는 더욱 더 작게 쪼개져서 관리되어야 할 청크 수가 증가한다. 이것은 프리 리스트를 검색하는 시간을 증가시키고 shared pool 래치에 대한 경합을 증가시켜서 성능 저하를 유발하거나 ORA-4031 에러를 발생시키게 된다.

    (3) 임시 조치 방법

    1) Shared pool 초기화 : 거의 해결 되는 경우가 없습니다!!!!!!

    SQL> alter system flush shared_pool;

    => Shared pool 내의 연속된 메모리 조각들을 하나의 조각으로 합쳐주는 역할.

    => 다른 SQL 정보 또한 Shared pool에서 제거하므로 해당 명령어를 수행한 후에 모든 SQL이 하드파싱을 수행하게 되어 성능 저하가 발생할 수 있음.

     

    2) 패치 등을 고려

    ORA-4031 에러는 오라클 버그로 등록된 부분이 있으므로 해당 오라클 버전을 확인하여 오라클 패치 적용 및 업그레이드 등을 고려.

     

    3) 파라미터 설정

    SHARED_POOL_RESERVED_SIZE 파라미터 설정을 통해 에러 감소.

     

    SQL> SELECT owner, name, type FROM v$db_object_cache
    2 WHERE sharable_mem > 10000
    3 AND (type=‘PACKAGE’ or type=‘PACKAGE BODY’ or
    4 type=‘FUNCTION’ or type=‘PROCEDURE’)
    5 AND KEPT=‘NO’;

     

    OWNER NAME TYPE
    ----- -------- ------------
    SYS STANDARD PACAKGE
    SYS STANDARD PACAKGE BODY
    SYS DBMS_UTILITY PACAKGE BODY

     

    Keeping Large Objects

    ------------------------

     Pin large packages in the library cache:


    SQL> EXECUTE dbms_shared_pool.keep(‘package_name’);


    SQL> SELECT owner, name, type FROM v$db_object_cache
    2 WHERE sharable_mem > 10000
    3 AND (type=‘PACKAGE’ or type=‘PACKAGE BODY’ or
    4 type=‘FUNCTION’ or type=‘PROCEDURE’)
    5 AND KEPT=‘NO’;


    OWNER NAME TYPE
    ----- -------- ------------
    SYS DBMS_UTILITY PACAKGE BODY

     

    4) Large pool 설정

    만약 병렬 프로세싱을 사용한다면 Large pool 설정으로 에러를 감소.

     

    5) 커서 공유

     .. OPEN_CURSORS


    .. CURSOR_SPACE_FOR_TIME


    .. SESSION_CACHED_CURSORS

    과도한 설정시 4031 에러 발생하는 경우 발생


    .. CURSOR_SHARING

     

    CURSOR_SHARING 파라미터를 설정함으로써 커서를 공유하게 함.

    - EXACT : 정확히 동일한 SQL문장인 경우만 커서를 공유. 기본값.

    - FORCE : SQL 문장은 같으며 리터럴 변수 값만 틀린 SQL에 대해 커서를 공유.

    - SIMILAR : 오라클이 실행계획을 판단해서 성능이 저하되지 않는다고 판단될 때 FORCE 설정과 동일하게 동작.

    그러나 FORCE나 SIMILAR로 설정한 경우 원하지 않는 실행계획 변화로 인한 성능 저하를 유발시킬 수 있으므로 운영 단계에서는 적용이 어려움.

    (4) 근본적인 조치 방법

    1) 하드 파싱을 많이 발생시키는 원인이 되는 리터럴SQL을 찾아서 바인드 변수를 사용하도록 변경해야 함.

    이미 개발이 완료되어 운영되고 있는 단계에서는 프로그램에서 수행되고 있는 SQL을 수정하기가 쉽지 않기 때문에, 미리 앞전에 개발 단계에서 하드 파스를 유발하는 SQL들을 찾아서 수정하는 것이 중요.

    또한 개발자들에 대한 교육을 통해서 개발 시 바인드 변수를 사용하도록 해야 함.

     

    2) Prepared Statement의 사용을 통해 JDBC 프로그램 내의 리터럴 SQL을 제거.

     

    3) 실제 shared pool size 가 작아서 발생하는 경우도 있음

     

    4) 운영중 발생시 db restart 하는게 대부분 임

  • 관리자 2011.05.17 22:16

    요약

    -----

    ORA-04031는 latch: shared pool와 연관성이 있습니다.
    latch: shared pool은 Freelist에 동일한 LCO 검색에 실패했을 경우 새로운 LCO를 생성하기 위해
    적절한 Free Chunk를 확보할때까지 shared pool latch를 획득하게 됩니다.
    최적 크기의 프리 청크가 존재하지 않으면 조금 더 큰 크기의 프리 청크를 찾아서 이를 split하여
    사용하며 남은 청크는 다시 프리 리스트로 등록시키며, 모든 프리 리스트를 탐색하고도
    적절한 크기의 프리 청크를 찾지 못하면 LRU 리스트를 탐색합니다.
    LRU 리스트의 청크들은 현재 핀(pin)되지 않은 재생성가능한 청크들이며,
    LRU 리스트를 탐색하고도 적절한 크기의 청크를 확보하지 못하면
    shared pool 내의 여유 메모리공간을 추가적으로 할당하고 이것마저도 실패한다면 ORA-4031 에러가 발생합니다

    이처럼 ORA-04031는 빈번한 하드 파싱에 의한 메모리 단편화 문제이며 해결방법으로는


    1. 사이즈가 큰 PL/SQL 블럭의 사용을 자제하며, 불가피하게 사이즈가 큰 PL/SQL 오브젝트는
       DBMS_SHARED_POOL.KEEP을 사용하여 Library Cache에 고정한다


    2. 가급적 SQL을 공유할 수 있도록 Bind SQL를 사용하거나, CURSOR_SHARING 파라미터 사용


    3. shared_pool_reserved_size의 사이즈를 증가시켜 Large Chunk을 위한 공간을 할당한다

    그리고 임시 조치방법으로는 instance 재기동 or flash shared pool 생각해 볼 수 있습니다

  • 관리자 2011.05.18 06:10
    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;
  • 관리자 2011.05.18 06:11

    혹시 알려진 제품 문제에 해당 되지 않는지 확인 한다.

    * BUG 1397603: ORA-4031 / SGA memory leak of PERMANENT memory occurs
    for buffer handles. (Workaround: _db_handles_cached=0, Fixed: 8172,
    901 )
    * BUG 1640583: ORA-4031 due to leak / cache buffer chain contention
    from AND-EQUAL access. (Fixed: 8171,901 )
    * BUG 1318267: INSERT AS SELECT statements may not be shared when they
    should be if TIMED_STATISTICS. It can lead to ORA-4031. (Workaround:
    _SQLEXEC_PROGRESSION_COST=0, Fixed: 8171, 8200)
    * BUG 1193003: Cursors may not be shared in 8.1 when they should be
    (Fixed: 8162, 8170, 901)


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
공지 Q&A Oracle관련 게시물만 Sean 2014.04.09 84854
16 Q&A alert_log는 5 perfstat 2011.04.22 8075
15 Q&A import후 simple_size 0일때 고구마 2014.01.13 3198
14 Q&A Local Prefixed와 Local Non-Prefixed index 차이 ? 1 고구마 2011.06.13 9602
» Q&A ORA--04031 에러에 대해 질문합니다. 4 고구마 2011.05.17 12661
12 Q&A ORACLE_BASE, ORACLE_HOME 각각의 위치에 대한 설명 부탁드립니다 Tech1 2012.03.20 10943
11 Q&A RAC 쿼리 속도 관련 질문입니다. 3 고구마 2011.05.20 8974
10 Q&A RHEL5에 11gR2 RAC 구성시 ASM 관련 에러 file 송기성 2010.12.11 17702
9 Q&A [답변] 테이블 수정, 삭제 및 LOB에 대해 알려주세요 Tech1 2012.03.26 10666
8 Q&A 서버교체에 관해서.. 1 명랑여행 2010.08.20 9645
7 Q&A 업그레이드 이후 안됨 3 유주환 2010.04.23 10387
6 Q&A 오라클 복구 질문 희달이 2015.05.08 1153
5 Q&A 오라클 유저 삭제가 안될시? 4 명랑여행 2010.05.27 11552
4 Q&A 오라클 프로세스에 대해서 궁금합니다. 4 고구마 2011.05.04 11196
3 Q&A 파티션 테이블 종류가 뭐가 있나요? 1 고구마 2011.06.13 10349
2 Q&A 파티션 테이블에 대해서 몇가지 궁금합니다. 1 고구마 2011.06.13 11835
1 Q&A 패키지 BODY 관련 질문 3 고구마 2011.05.03 10223
Board Pagination Prev 1 Next
/ 1