コミットチューニング

コミットチューニング

最近友たちにコミット件数とチューニングのことについて聞かれた。説明するため、具体的なサンプルを作った。
ここで公開しておく。

考えられる遅延原因は
PLエンジン, SQLエンジンの切替
コミットの関連処理
REDOログの同期
LATCHの割り当て
UNDOとRBS
RBSの処理
UNDOの拡張
IOとキャッシュ



下記の結論である。
グラフの示したとおり、一度コミットする行数は多すぎでも少なすぎでも遅くなる。今回の場合100行単位でコミットする処理が一番早い。
同じ全部コミットで処理する場合（今回は100000件）、プロシージャはSQLに比べてやや遅い。
具体的な件数はサーバによって違う。テスト環境はノートPCだから、100件は最適だが、普通のサーバは10000～100000件が最適。

検証の詳細手順は下記のとおり：

-- 準備
set lines 150
set pages 9999
set timi on
set time on
spool txtest.log
 
-- テーブルの作成
create table t_txtest (tno number(7, 0), value varchar2(10));
-- データの作成
-- 1000000件
insert into t_txtest select rownum, rownum 
from (select 0 zero from dba_objects where rownum <= 10000), 
        (select 0 zero from dba_objects where rownum <= 100);
-- 100000件
insert into t_txtest select rownum, rownum 
from (select 0 zero from dba_objects where rownum <= 10000), 
        (select 0 zero from dba_objects where rownum <= 10);
 
-- プロシージャーの作成
create or replace procedure prc_txtest(cmt_n pls_integer) is
i pls_integer := 0;
begin
    for x in (select t.rowid rid, t.* from t_txtest t) loop
        if i = cmt_n then
            commit;
            i := 0;
        else
            i := i + 1;
        end if;
        update t_txtest set value = x.value where rowid = x.rid;
    end loop;
    commit;
end;
 
-- 検証の実施
-- 全部キャッシュさせる
update t_txtest set value = value;
exec prc_txtest(100);
update t_txtest set value = value;
 
-- 検証の本番
exec prc_txtest(1);
exec prc_txtest(100);
exec prc_txtest(1000);
exec prc_txtest(10000);
update t_txtest set value = value;

そして結果は下記のとおり。

時間        オペレーション
01:17.1    exec prc_txtest(1)
00:56.2    exec prc_txtest(10)
00:50.8    exec prc_txtest(100)
00:55.3    exec prc_txtest(1000)
00:57.2    exec prc_txtest(10000)
01:09.9    exec prc_txtest(100000)
00:59.5    update t_txtest set value = value