ORA-4031エラーの生成および診断の方法

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")

本当に簡単でしょう？

オラクルは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ダンプを残す必要があります。

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ダンプの意味はここで説明しています)

ロウトレースファイルはあんまり読みにくいから、私は自分で作ったheap_analyze.sqlスクリプトを利用してより意味のある情報を作り上げます。次のレポートを見てください。

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
...

レポートの詳細な解析は皆さんに任せます。一つだけ言えば、このレポートだけでもORA-4031エラーの原因を相当に正確に見つけることが出来るというものです。
(heap_analyze.sqlの定義はここにあります)

SGAのHeapダンプにとって気にしなければならないのはこのオペレーションはlatchを過度に使用するというものです。従って本番環境で適用する時はシステムが失敗する可能性を甘受しなければなりません。