태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

ORA-4031 에러 만들기

오라클 2009.08.11 17:40
ORA-4031 에러를 직접 만들어 본 적이 있나요? 아래에 제가 테스트할 때 사용하는 방법을 소개합니다.
UKJA@ukja102> alter system set sga_target=0;

System altered.

Elapsed: 00:00:00.01
UKJA@ukja102> 
UKJA@ukja102> alter system set db_cache_size=100m;

System altered.

Elapsed: 00:00:02.39
UKJA@ukja102> alter system set shared_pool_size=200m;

System altered.

Elapsed: 00:00:00.03
UKJA@ukja102> -- open many cursors and SQL statements 
UKJA@ukja102> create or replace procedure proc_4031(p_depth in number)
  2  is
  3   v_cursor  sys_refcursor;
  4   v_sql    varchar2(20000);
  5  begin
  6   v_sql := 'select 1 ' || rpad(' ', 4000) || rpad(' ', 4000) || rpad(' ', 4000)
  7        || rpad(' ', 4000) || 'from dual a_' || p_depth;
  8  
  9   open v_cursor for v_sql;
 10  
 11   proc_4031(p_depth+1);
 12  
 13  end;
 14  /

Procedure created.

Elapsed: 00:00:00.11
UKJA@ukja102> -- allow tons of cursors open
UKJA@ukja102> alter system set open_cursors = 65535 scope=memory;

System altered.

Elapsed: 00:00:00.01

UKJA@ukja102> exec proc_4031(1);
BEGIN proc_4031(1); END;

*
ERROR at line 1:
ORA-04031: unable to allocate 536 bytes of shared memory ("shared
pool","unknown object","sga heap(1,1)","library cache")
아주 쉽죠? 1) 적은 크기의 Shared Pool, 2) 많은 수의 Open Cursor, 3) 큰 크기의 SQL Text를 조합해서 Shared Pool이 꽉 차게 만들어버립니다. Oracle은 ORA-4031 에러가 발생하면 다음과 같은 간단한 정보를 트레이스 파일에 남깁니다.(이것이 자동화되어 있다는 것만으로도 ORA-4031 에러가 얼마나 치명적인 에러인지 간접적으로 알 수 있습니다)
=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors.  This trace may be disabled
by setting the init.ora _4031_dump_bitvec = 0
...

==============================
Memory Utilization of Subpool 1
================================
     Allocation Name          Size   
_________________________  __________
"free memory              "     2092180  
"sql area                 "    10322144  
"row cache                "     3741868  
"CCursor                  "     4475368  
"PCursor                  "     2462740  
"kglsim hash table bkts   "     2097152  
"KCB Table Scan Buffer    "     3981204  
"PL/SQL DIANA             "     1081972  
"ASH buffers              "     4194304  
"PL/SQL MPCODE            "     1994296  
"KQR M PO                 "     2143744  
"KGLS heap                "     2961796  
"library cache            "    13010768  
"Heap0: KGL               "     1167984  
...
문제는 대부분의 경우 위와 같은 간단한 정보만으로는 더 이상의 분석이 쉽지 않다는 것입니다. 따라서 다음과 같이 매뉴얼하게 Heap Dump를 수행해야할 경우가 많습니다.
UKJA@ukja102> alter session set events '4031 trace name heapdump level 0x20000002, lifetime 1';

Session altered.

Elapsed: 00:00:00.01
UKJA@ukja102> alter session set "_4031_dump_bitvec" = 0;

Session altered.

Elapsed: 00:00:00.01
UKJA@ukja102> 
UKJA@ukja102> exec proc_4031(1);
BEGIN proc_4031(1); END;

*
ERROR at line 1:
ORA-04031: unable to allocate 800 bytes of shared memory ("shared
pool","unknown object","sga heap(1,1)","library cache")
sga heap(1,1)풀에서800바이트의 메모리를 획득하지 못해서ORA-4031에러가 발생한 것으로 보고됩니다. (레벨0x20000002의Heap덤프의 의미는 여기에서잘 설명하고 있습니다)

Raw 트레이스 파일은 상당히 보기가 어렵기 때문에, 저 같은 경우에는 제가 만든 간단한 스크립트를 이용해서 좀 더 가공된 정보를 봅니다.

UKJA@ukja102> @heap_analyze &trace_file

01. size per heap

HEAP_NAME                   HSZ
-------------------- ----------
sga heap(1,1)              96.0  
sga heap(1,0)              40.0
sga heap(1,3)              32.0
sga heap(1,2)              32.0
KSFD SGA I/O b              3.8
CURSOR STATS                2.1
...


02. size per chunk type

HEAP_NAME            CHUNK_TYPE           CNT         SZ        HSZ HRATIO
-------------------- --------------- -------- ---------- ---------- ------
CURSOR STATS         free                  78         .0        2.1     .5
CURSOR STATS         freeable          12,359        2.0        2.1   99.5
...
sga heap(1,1)        R-free                24        4.9       96.0    5.1
sga heap(1,1)        freeable          14,520        5.2       96.0    5.4
sga heap(1,1)        recreate          13,150       85.7       96.0   89.3
sga heap(1,1)        free               1,016         .2       96.0     .2
sga heap(1,1)        R-freeable            48         .0       96.0     .0
...

03. size per object type

HEAP_NAME            OBJ_TYPE                  CNT         SZ        HSZ HRATIO
-------------------- -------------------- -------- ---------- ---------- ------
...
sga heap(1,1)        plwppwp:garbage             1         .0       96.0     .0
sga heap(1,1)        listener addres             1         .0       96.0     .0
sga heap(1,1)        KGL handles            12,376       81.3       96.0   84.6
sga heap(1,1)                                1,040        5.1       96.0    5.3
sga heap(1,1)        library cache          13,797        4.0       96.0    4.2
sga heap(1,1)        KSFD SGA I/O b              1        3.8       96.0    4.0
...

05. freelists histogram

HEAP_NAME            HIST                 CNT         SZ        HSZ HRATIO
-------------------- --------------- -------- ---------- ---------- ------
sga heap(1,1)        (16~32)               33         .0         .2     .4
sga heap(1,1)        (32~64)              140         .0         .2    3.4
sga heap(1,1)        (64~128)             256         .0         .2   12.8
sga heap(1,1)        (128~256)            253         .1         .2   26.9
sga heap(1,1)        (256~512)            330         .1         .2   55.0
sga heap(1,1)        (512~1024)             4         .0         .2    1.5
...

위의 결과에 대한 해석은 관심있으신 분들의 숙제로 남깁니다. :) (heap_analyze.sql의 정의는 여기에 있습니다)

SGA Heap 덤프를 수행할 때 한가지 조심할 점은 덤프를 수행하는 과정이 Latch를 매우 과도하게 사용한다는 것입니다. 따라서 운영 환경에서는 대단히 조심스럽게(가령 더 이상 다른 분석 데이터가 없을 때) 사용해야 합니다. 이런 면에서 SGA Heap 문제보다는 PGA Heap 문제가 더 다루기 쉽다고 할 수 있겠네요.

저작자 표시
신고

'오라클' 카테고리의 다른 글

CLOB Concatentaion 줄이기  (2) 2009.08.21
Alert Log 파일 분석 자동화하기  (10) 2009.08.19
ORA-4031 에러 만들기  (9) 2009.08.11
O3 책의 중요한 오류  (0) 2009.08.10
재현가능한 Test Case를 만들기가 어려운 이유  (0) 2009.08.05
tags :
Trackback 0 : Comments 9
  1. extremedb 2009.08.12 01:18 신고 Modify/Delete Reply

    10g R2 에서 나온 Replay 기능을 사용한다면 운영 DB 가 아닌 데서 덤프를 수행할수 있겠네요.
    좋은 정보 감사합니다.

    • 욱짜 2009.08.12 14:31 신고 Modify/Delete

      Oracle 10g에서 제공되는 Preliminary Connection을 사용하면 Latch를 획득하지 않고 Direct Memory Access로 특정 작업을 수행할 수 있는데, 안타깝게도 SGA Heap Dump는 불가능한거 같습니다.

  2. extremedb 2009.08.12 14:42 신고 Modify/Delete Reply

    그렇군요. DMA 는 언제봐도 매력적 입니다. 심지어 Hang 상태에서도 동작 하더군요.

    • 욱짜 2009.08.12 14:46 신고 Modify/Delete

      ㅎㅎ 넵.

      DMA는 제가 일하는 엑셈의 Maxgauge같은 툴들이 팔릴 수 있는 기술적인 근거가 되기도 합니다. :)

  3. 유수익 2009.09.02 11:24 신고 Modify/Delete Reply

    음.. 언제 4031 발생시 분석하는 기법을 강의 하시면 어떨까요?
    실상 발생하면 대처할 수 있는 방법이 거의 전무(init 파일에 4031 event를 설정해도 오라클에서는 명확한
    답을 주지 못함)한 상태거든요..

  4. 욱짜 2009.09.02 15:02 신고 Modify/Delete Reply

    4031 에러는 가장 까다로운 에러 중 하나죠. 기회가 되면 꼭 해보겠습니다.

  5. 유수익 2009.09.02 15:42 신고 Modify/Delete Reply

    또한, 위에것처럼 proc_4031를 만드는 ora-01000 최대 열기 커서 수를 초과했습니다. 라는 메시지가 나오네요
    다른것을 설정해야 하나요?

    • 욱짜 2009.09.03 10:33 신고 Modify/Delete

      이런 것들을 한번 시도해보세요.
      - Shared Pool Size를 줄여 본다.
      - SQL Text를 더 길게 해본다.
      - 동시에 여러 세션에서 위의 작업을 수행해본다.

  6. 윤봉운 2011.04.02 10:46 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment

티스토리 툴바