ORA-4031 에러 만들기

ORACLE/TroubleShooting2011. 6. 2. 14:17

ORA-4031 에러 만들기

출처 : http://ukja.tistory.com/239

오라클 2009/08/11 17:40

ORA-4031 에러를 직접 만들어 본 적이 있나요? 아래에 제가 테스트할 때 사용하는 방법을 소개합니다.

01
UKJA@ukja102> alter system set sga_target=0; 

02
  
03
System altered. 

04
  
05
Elapsed: 00:00:00.01 

06
UKJA@ukja102>  

07
UKJA@ukja102> alter system set db_cache_size=100m; 

08
  
09
System altered. 

10
  
11
Elapsed: 00:00:02.39 

12
UKJA@ukja102> alter system set shared_pool_size=200m; 

13
  
14
System altered. 

15
  
16
Elapsed: 00:00:00.03 

17
UKJA@ukja102> -- open many cursors and SQL statements  

18
UKJA@ukja102> create or replace procedure proc_4031(p_depth in number) 

19
  2  is

20
  3   v_cursor  sys_refcursor; 

21
  4   v_sql    varchar2(20000); 

22
  5  begin

23
  6   v_sql := 'select 1 ' || rpad(' ', 4000) || rpad(' ', 4000) || rpad(' ', 4000) 

24
  7        || rpad(' ', 4000) || 'from dual a_' || p_depth; 

25
  8   

26
  9   open v_cursor for v_sql; 

27
 10   

28
 11   proc_4031(p_depth+1); 

29
 12   

30
 13  end; 

31
 14  / 

32
  
33
Procedure created. 

34
  
35
Elapsed: 00:00:00.11 

36
UKJA@ukja102> -- allow tons of cursors open 

37
UKJA@ukja102> alter system set open_cursors = 65535 scope=memory; 

38
  
39
System altered. 

40
  
41
Elapsed: 00:00:00.01 

42
  
43
UKJA@ukja102> exec proc_4031(1); 

44
BEGIN proc_4031(1); END; 

45
  
46
* 

47
ERROR at line 1: 

48
ORA-04031: unable to allocate 536 bytes of shared memory ("shared 

49
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 에러가 얼마나 치명적인 에러인지 간접적으로 알 수 있습니다)

01
================================= 

02
Begin 4031 Diagnostic Information 

03
================================= 

04
The following information assists Oracle in diagnosing 

05
causes of ORA-4031 errors.  This trace may be disabled 

06
by setting the init.ora _4031_dump_bitvec = 0 

07
... 

08
  
09
============================== 

10
Memory Utilization of Subpool 1 

11
================================ 

12
     Allocation Name          Size   

13
_________________________  __________ 

14
"free memory              "     2092180   

15
"sql area                 "    10322144   

16
"row cache                "     3741868   

17
"CCursor                  "     4475368   

18
"PCursor                  "     2462740   

19
"kglsim hash table bkts   "     2097152   

20
"KCB Table Scan Buffer    "     3981204   

21
"PL/SQL DIANA             "     1081972   

22
"ASH buffers              "     4194304   

23
"PL/SQL MPCODE            "     1994296   

24
"KQR M PO                 "     2143744   

25
"KGLS heap                "     2961796   

26
"library cache            "    13010768   

27
"Heap0: KGL               "     1167984   

28
...

문제는 대부분의 경우 위와 같은 간단한 정보만으로는 더 이상의 분석이 쉽지 않다는 것입니다. 따라서 다음과 같이 매뉴얼하게 Heap Dump를 수행해야할 경우가 많습니다.

01
UKJA@ukja102> alter session set events '4031 trace name heapdump level 0x20000002, lifetime 1'; 

02
  
03
Session altered. 

04
  
05
Elapsed: 00:00:00.01 

06
UKJA@ukja102> alter session set "_4031_dump_bitvec" = 0; 

07
  
08
Session altered. 

09
  
10
Elapsed: 00:00:00.01 

11
UKJA@ukja102>  

12
UKJA@ukja102> exec proc_4031(1); 

13
BEGIN proc_4031(1); END; 

14
  
15
* 

16
ERROR at line 1: 

17
ORA-04031: unable to allocate 800 bytes of shared memory ("shared 

18
pool","unknown object","sga heap(1,1)","library cache")

sga heap(1,1)풀에서800바이트의 메모리를 획득하지 못해서ORA-4031에러가 발생한

것으로 보고됩니다. (레벨0x20000002의Heap덤프의 의미는 여기에서잘 설명하고 있습니다)

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

01
UKJA@ukja102> @heap_analyze &trace_file 

02
  
03
01. size per heap 

04
  
05
HEAP_NAME                   HSZ 

06
-------------------- ---------- 

07
sga heap(1,1)              96.0   

08
sga heap(1,0)              40.0 

09
sga heap(1,3)              32.0 

10
sga heap(1,2)              32.0 

11
KSFD SGA I/O b              3.8 

12
CURSOR STATS                2.1 

13
... 

14
  
15
  
16
02. size per chunk type 

17
  
18
HEAP_NAME            CHUNK_TYPE           CNT         SZ        HSZ HRATIO 

19
-------------------- --------------- -------- ---------- ---------- ------ 

20
CURSOR STATS         free                  78         .0        2.1     .5 

21
CURSOR STATS         freeable          12,359        2.0        2.1   99.5 

22
... 

23
sga heap(1,1)        R-free                24        4.9       96.0    5.1 

24
sga heap(1,1)        freeable          14,520        5.2       96.0    5.4 

25
sga heap(1,1)        recreate          13,150       85.7       96.0   89.3 

26
sga heap(1,1)        free               1,016         .2       96.0     .2 

27
sga heap(1,1)        R-freeable            48         .0       96.0     .0 

28
... 

29
  
30
03. size per object type 

31
  
32
HEAP_NAME            OBJ_TYPE                  CNT         SZ        HSZ HRATIO 

33
-------------------- -------------------- -------- ---------- ---------- ------ 

34
... 

35
sga heap(1,1)        plwppwp:garbage             1         .0       96.0     .0 

36
sga heap(1,1)        listener addres             1         .0       96.0     .0 

37
sga heap(1,1)        KGL handles            12,376       81.3       96.0   84.6 

38
sga heap(1,1)                                1,040        5.1       96.0    5.3 

39
sga heap(1,1)        library cache          13,797        4.0       96.0    4.2 

40
sga heap(1,1)        KSFD SGA I/O b              1        3.8       96.0    4.0 

41
... 

42
  
43
05. freelists histogram 

44
  
45
HEAP_NAME            HIST                 CNT         SZ        HSZ HRATIO 

46
-------------------- --------------- -------- ---------- ---------- ------ 

47
sga heap(1,1)        (16~32)               33         .0         .2     .4 

48
sga heap(1,1)        (32~64)              140         .0         .2    3.4 

49
sga heap(1,1)        (64~128)             256         .0         .2   12.8 

50
sga heap(1,1)        (128~256)            253         .1         .2   26.9 

51
sga heap(1,1)        (256~512)            330         .1         .2   55.0 

52
sga heap(1,1)        (512~1024)             4         .0         .2    1.5 

53
...

위의 결과에 대한 해석은 관심있으신 분들의 숙제로 남깁니다. :) (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 : ORA-4031

Trackback 0 : Comments 9

Trackback Address :: http://ukja.tistory.com/trackback/239

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는 불가능한거 같습니다.
extremedb 2009/08/12 14:42 Modify/Delete Reply
그렇군요. DMA 는 언제봐도 매력적 입니다. 심지어 Hang 상태에서도 동작 하더군요.
- 욱짜 2009/08/12 14:46 Modify/Delete
  ㅎㅎ 넵.
  
  DMA는 제가 일하는 엑셈의 Maxgauge같은 툴들이 팔릴 수 있는 기술적인 근거가 되기도 합니다.
유수익 2009/09/02 11:24 Modify/Delete Reply
음.. 언제 4031 발생시 분석하는 기법을 강의 하시면 어떨까요?
실상 발생하면 대처할 수 있는 방법이 거의 전무(init 파일에 4031 event를 설정해도 오라클에서는 명확한
답을 주지 못함)한 상태거든요..
욱짜 2009/09/02 15:02 Modify/Delete Reply
4031 에러는 가장 까다로운 에러 중 하나죠. 기회가 되면 꼭 해보겠습니다.
유수익 2009/09/02 15:42 Modify/Delete Reply
또한, 위에것처럼 proc_4031를 만드는 ora-01000 최대 열기 커서 수를 초과했습니다. 라는 메시지가 나오네요
다른것을 설정해야 하나요?
- 욱짜 2009/09/03 10:33 Modify/Delete
  이런 것들을 한번 시도해보세요.
  - Shared Pool Size를 줄여 본다.
  - SQL Text를 더 길게 해본다.
  - 동시에 여러 세션에서 위의 작업을 수행해본다.
윤봉운 2011/04/02 10:46 Modify/Delete Reply
관리자의 승인을 기다리고 있는 댓글입니다

저작자표시 비영리

'ORACLE > TroubleShooting' 카테고리의 다른 글

How to Disable Asynch_io on HP to Avoid Ioctl Async_config Error Errno = 1 (0)	2011.07.04
ora 4031 에러 처리 방안 (0)	2011.06.02
ORA-03113 on Unix - What Information to Collect [ID 17613.1] (0)	2011.05.02
DIAGNOSING ORA-3113 ERRORS [ID 1020463.6] (0)	2011.05.02
Thread 1 cannot allocate new log, sequence (0)	2011.03.25

Posted by [PineTree]

About DATABASE