一顧客社で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を使うときはこの点に注意する必要があります。
Posts
