INSERT...VALUESコマンドとAPPENDヒント

1つの行のインサートにAPPENDヒントを用いたDirect Path Modeが必要なのかよく分かりません。とにかく、オラクルは11gからこのようなモードを支援するようにしました。すなわちINSERT...VALUES...コマンドでもAPPENDヒントが動作するように修正されました。もっと詳細に整理してみれば...

• Oracle 10gまではINSERT...VALUES...コマンドにAPPENDヒントを与えても無視されます。すなわち、APPENDヒントはINSERT...SELECT...コマンドでだけ動作します。
  
  
• APPENDヒントによってDirect Path Modeで動作すると行をインサートするとき現在セグメントの空きブロックを無視し（同時にバッファーキャッシュを経緯しなくて）セグメントのHigh Water MarkのあとにAppendさせてしまいます。したがって大量のデータを追加する時速度は速いがデータファイルにその分不要な空き容量が残りかねません。（でもこの空き容量もあとで使用されるはずです）
  
  
• Oracle 11gR1ではINSERT...VALUES...コマンドでもAPPENDヒントが動作します。隠しパラメーター_direct_path_insert_featuresで制御できます。
  
  
• Oracle 11gR2ではINSERT...VALUES...コマンドでAPPENDヒントを与えて無視されます。かわりにAPPEND_VALUESというヒントが追加されました。すなわちAPPEND_VALUESヒントを使用すればINSERT...VALUES...コマンドでもDirect Path Modeでインサートされます。
  

少し複雑になったでしょう。下記は簡単なテスト結果です。

create table t1(c1 number);

insert into t1 values(1);
commit;
insert into t1 values(2);
commit;

-- （2件の行をインサートして、Conventional Path Modeであるため二つの行が同じブロックにあるはずです）
select
 dbms_rowid.rowid_relative_fno(rowid) as file#,
 dbms_rowid.rowid_block_number(rowid) as block#
from t1;

-- 10.2.0.1
     FILE#     BLOCK#
---------- ----------
         4        853
         4        853

-- 11.1.0.6
     FILE#     BLOCK#
---------- ----------
         4        758
         4        758

-- （APPENDヒントが与えられたINSERT...VALUES...コマンドです）
insert /*+ append */ into t1 values(3);
commit;

-- 10.2.0.1ではAPPENDヒントが無視されるためやはり同じブロックにインサートされます。
     FILE#     BLOCK#
---------- ----------
         4        853
         4        853
         4        853

-- でも11.1.0.6ではAPPENDヒントが動作し、その結果High Water Markの以後にインサートされてしまいます。
     FILE#     BLOCK#
---------- ----------
         4        758
         4        758
         4        769

-- 11.2.0.1ではAPPEND_VALUESヒントがその役割を代わります。
iinsert /*+ append_values */ into t1 values(3);
commit;

     FILE#     BLOCK#
---------- ----------
         4        526
         4        526
         4        528

なぜオラクル開発者たちが急にINSERT...VALUES...コマンドでもAPPENDヒントが動作することに変えたんでしょうか。いくら考えてみてもわかりません。誰かその理由が浮かんでくる方あります?

PGAサイズの非正常的な増加

PGAサイズが多すぎるとシステムに多い問題を起こします。非正常的なPGAサイズの問題を分析する最高の方法はPGAヒープダンプを落としてそれを精密分析することです。しかし、PGAのサイズ増加が速すぎる場合は手動的にダンプコマンドを実行するのはむずかしいはずです。

幸い、オラクルが提供する診断イベントを活用すればPGAヒープダンプを自動的かすることができます。

1. まず、10261診断イベントを通じてPGAヒープダンプサイズを制限します。たとえば、下記のコマンドでPGAヒープサイズを100000KBに制限できます。

alter system set events '10261 trace name context forever, level 100000';

2. 10261イベントが存在する場合、PGAのサイズが設定したサイズを超えるプロセスはORA-600 [723]エラーとともに失敗します。

-- make big pga
declare
 type varchar2_array is table of varchar2(32767) index by pls_integer;
 vc  varchar2_array;
 v  varchar2(32767);
begin
 for idx in 1 .. 10000 loop
 v := rpad('x',32767,'x');
 vc(idx) := v;
 end loop;
end;
/

ERROR at line 1:
ORA-00600: internal error code, arguments: [723], [41000], [pga heap], [], [],
[], [], []

3. この時、600診断イベントをPGAヒープダンプを実行するように設定すれば、10261→600→ヒープダンプの手順で自動的にPGAヒープダンプが実行されます。

alter system set events '600 trace name heapdump level 0x20000001';

4. 下記は自動的に生成されたPGAヒープダンプの一部です。特に、ダンプレベル0x20000001のおかげで下位Subheapを含んだダンプが記録されます。

DDE: Problem Key 'ORA 600 [723]' was flood controlled (0x2) (incident: 44800)
ORA-00600: internal error code, arguments: [723], [41000], [pga heap], [], [], [], [], []
****** ERROR: PGA size limit exceeded: 102450812 > 102400000 *****
******************************************************
HEAP DUMP heap name="pga heap"  desc=11AFB098
 extent sz=0x206c alt=92 het=32767 rec=0 flg=2 opc=1
 parent=00000000 owner=00000000 nex=00000000 xsz=0xfff8 heap=00000000
 fl2=0x60, nex=00000000
EXTENT 0 addr=39150008
  Chunk 39150010 sz=    24528    free      "               "
  Chunk 39155fe0 sz=    40992    freeable  "koh-kghu call  "  ds=0D4D9A60
EXTENT 1 addr=39140008
  Chunk 39140010 sz=    24528    free      "               "
  Chunk 39145fe0 sz=    40992    freeable  "koh-kghu call  "  ds=0D4D9A60
...

5. 最後の段階はヒープダンプを分析してどんなオブジェクトがヒープを使用しているのかを見破ることです。例えば、私はティパックという個人的なライブラリーを使用します。

select * from table(tpack.heap_file_report('C:\oracle\diag\rdbms\ukja1106\ukja1106\trace\ukja1106_ora_3640.trc'));

TYPE     HEAP_NAME        ITEM             ITEM_COUNT  ITEM_SIZE  HEAP_SIZE      RATIO
-------- ---------------- ---------------- ---------- ---------- ---------- ----------
HEAP     pga heap                                   0      97.14      97.14        100
HEAP     top call heap                              0        .18        .18        100
HEAP     top uga heap                               0        .31        .31        100
CHUNK    pga heap         free                   1554       36.2       97.1       37.3
CHUNK    pga heap         recreate                  9          0       97.1          0
CHUNK    pga heap         perm                     14          0       97.1          0
CHUNK    pga heap         freeable               1597       60.7       97.1       62.5
CHUNK    top uga heap     recreate                  1          0         .3       19.9
CHUNK    top uga heap     free                      5          0         .3          0
CHUNK    top uga heap     freeable                  4         .2         .3       79.9
CHUNK    top call heap    free                      3         .1         .1       65.5
CHUNK    top call heap    recreate                  2          0         .1          1
CHUNK    top call heap    freeable                  1          0         .1       33.3
CHUNK    top call heap    perm                      1          0         .1          0
OBJECT   pga heap         kews sqlstat st           1          0       97.1          0
OBJECT   pga heap         pesom.c:Proces            3          0       97.1          0
...

6. オラクルの診断イベントのほかに、V$SESSTATビューをモニターリングしながらメモリーサイズがあるサイズを超える時、PGAヒープダンプを行なう方法もあります。例えば、前述のティパックライブラリーを次のように利用できます。

col report_id new_value report_id

select tpack_server.create_report('Heap Dump') as report_id from dual;

exec tpack_server.add_parameter('&report_id', 'dump_level', '0x20000001');
exec tpack_server.add_parameter('&report_id', 'get_whole_contents', 0);

exec tpack_server.add_condition('&report_id', 'STAT', 'session pga memory', '>100000000', 'SUM');

exec tpack_server.register_report('&report_id');

-- start server
exec tpack_server.start_server;

PGAヒープサイズが100000000Bに達すると、ティパックはあらかじめ定義されているプロシージャを実行してヒープダンプを落とします。

Fri Jun 11 06:19:10 GMT+00:00 2010 : Session 142 got! sum=659645392, name = session pga memory
...
Fri Jun 11 06:27:50 GMT+00:00 2010 : executing report 1:142:1973827792 for session 142
Fri Jun 11 06:27:55 GMT+00:00 2010 : executing report = begin tpack.heap_dump(  dump_level=>'0x20000001', get_whole_contents=>0,  session_id => 142); end;
...

10261と600診断イベントは仮の方便にすぎないでしょう。最も重要なのはヒープダンプを注意深く分析してPGAサイズの非正常的な増加を防ぐことです。