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 문제가 더 다루기 쉽다고 할 수 있겠네요.