조회 수 12747 추천 수 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 84911
80 Tip Analyze 통계정보 dbkill 2010.04.11 13298
79 TM TDE (column, tablesapce) 1 file 윤현 2011.12.27 13243
78 Tip 세마포어에 대하여 고구마 2010.04.19 13232
77 Tip NL JOIN에 대하여... 1 고구마 2011.03.21 13165
76 TM SQL 튜닝 실무사례 2 1 file 고구마 2010.04.28 13075
75 TM SQL튜닝 실무사례 3 file 고구마 2010.04.28 13058
74 Tip Outer Join의 정확한 이해 file 고구마 2011.03.21 13028
73 Tip LIBRARY CACHE PIN 고구마 2011.11.10 12949
72 Tip ora-3113 에러 분석 접근관련... 고구마 2011.06.14 12790
» Q&A ORA--04031 에러에 대해 질문합니다. 4 고구마 2011.05.17 12747
70 Tip hardparse 발생및 세션 검사 2 도로시 2011.08.17 12666
69 TM ARDCI file 이현정 2012.10.09 12536
68 TM sql*plus에서 autotrace 설정 1 file 고구마 2010.04.06 12520
67 Tip 특정 DB USER의 SESSION수를 제한하는 방법 흑수건 2011.10.01 12390
66 TM SQL문 수행 내부적 처리절차 1 고구마 2010.04.16 12374
65 TM snapshot 쉽게 사용하는 방법 2 file dbkill 2010.04.06 12315
64 TM oracle DBMS package 1 file dbkill 2010.04.18 12313
63 TM 10g SHRINK 소개 file 승현짱 2010.04.21 12216
62 TM LINUX 10G ASM 튜닝 file 고구마 2010.04.09 12080
61 Tip ORACLE 10G CONVERTING TABLESPACES WITH THE RMAN CONVERT 1 흑수건 2011.10.20 11949
Board Pagination Prev 1 2 3 4 5 6 7 8 Next
/ 8