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