LOB Concatenationの性能問題

一顧客社でPL/SQLの内で300KBを超えるSQL文章を動的に生成する必要ができました。

最初に使用した方法は次のようでした。

(本来のソースコードを簡単なバージョンに変えた事を知らせて差し上げます)

UKJA@ukja1021> create or replace function make_long_sql(p_idx in number)
  2  return clob
  3  is
  4        v_clob      clob;
  5  begin
  6        v_clob := 'select ';
  7        for idx in 1 .. p_idx loop
  8         v_clob := v_clob||to_char(p_idx)||', '||to_char(p_idx+1)||','||to_char(p_idx+2)||',';
  9        end loop;
 10        v_clob := v_clob || '1 from dual';
 11  
 12        return v_clob;
 13  end;
 14  /

Function created.

どんな特別な事項も見つけられません。CLOB変数をConcatenationを通じて合わせるとても簡単な作業です。すなわち、改善の事項がぜんぜんないように見えます。

どころが、この関数を実行してみればなんと46秒が掛かります。

UKJA@ukja1021> exec :c := make_long_sql(10000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:46.00

実際の顧客社では150KB程度の文字列を動的に生成するのに5分以上が掛かりました。

私の処方は次のようにかりにVARCHAR2変数を使ってCLOBに対したConcatenationの回数を減らすのでした。

UKJA@ukja1021> create or replace function make_long_sql2(p_idx in number)
  2  return clob
  3  is
  4        v_clob      clob;
  5        v_varchar      varchar2(1000);
  6  begin
  7        v_clob := 'select ';
  8        for idx in 1 .. p_idx loop
  9         v_varchar := to_char(p_idx)||', '||to_char(p_idx+1)||','||to_char(p_idx+2)||',';
 10         v_clob := v_clob||v_varchar;
 11        end loop;
 12        v_clob := v_clob || '1 from dual';
 13  
 14        return v_clob;
 15  end;
 16  /

Function created.

結果はとても劇的です。実行時間が42秒から0.5秒に減りました。

UKJA@ukja1021> exec :c := make_long_sql2(10000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.50

実際の顧客社では5分の作業が5秒程度に減りました。

どこからこんな大きい違いが起こってありますか。V$SESSTATビューとV$LATCHビューを通じてどんな違いがあるかを見れば、ヒントを得ることができます。

NAME                                           VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
session logical reads                      17,376,991      133,035  -17,243,956
db block gets from cache                   15,764,933      102,971  -15,661,962
db block gets                              15,764,933      102,971  -15,661,962
consistent changes                          6,024,802       17,697   -6,007,105
db block changes                            6,024,814       17,710   -6,007,104
consistent gets from cache                  1,612,058       30,064   -1,581,994
consistent gets                             1,612,058       30,064   -1,581,994
free buffer requested                       1,432,143           49   -1,432,094
calls to get snapshot scn: kcmgss             480,030       40,019     -440,011
lob writes                                    120,008       10,004     -110,004
lob writes unaligned                          119,962       10,004     -109,958
lob reads                                      60,001       10,001      -50,000
...

LATCH_NAME                         D_GETS   D_MISSES   D_SLEEPS  D_IM_GETS
------------------------------ ---------- ---------- ---------- ----------
cache buffers chains            -45091211          0          0   -1399062
object queue header operation    -2864253          0          0          0
cache buffers lru chain          -2864138          0          0          0
simulator hash latch              -164417          0          0          0
simulator lru latch               -112459          0          0     -51952
...

VARCHAR2変数を一時に使う場合にはLogical Reads, LOB Reads, LOB Writesなどが大きく減り、そのほどlatch獲得でも違いが発生します。これが性能の違いで現れたのです。

その理由は下のポストでよく説明しています。

http://jonathanlewis.wordpress.com/2009/07/09/concatenating-lobs/

LOBはConcatenationに脆弱な特徴を持っているから、Concatenationの回数および位置が性能に大きい影響を与える可能性があります。LOBを使うときはこの点に注意する必要があります。