Oracle で大量の insert を高速に処理する方法の検討

久々に Oracle ネタです。以前　セッション管理に向いているデータベースは MySQL ? Oracle ?　というエントリを書いたのですが、それとは別に insert の処理をどうしても高速化する必要に迫られました。今回高速化すべき要件として同一テーブルに大量のデータを一気に登録する作業です。いわゆる管理系業務で良くある csv のインポート的な機能に相当します。

さてそのような要件の場合、insert の方法は大きく分けて以下の方法が考えられます。それぞれどんなものかを知りたい方は本エントリー最後の参考を読むと理解できると思います。

  1. アプリ側から csv のデータを読み込んで for 文で件数分ループして insert する
  2. マルチテーブルインサート機能を用いて、１つの insert で複数データを一括登録する
  3. バルクインサート処理を行う PL/SQL を定義して一括登録する
  4. SQL *Loader をバックエンドで実行させて一括登録する

- スポンサーリンク -

さて次に検証と実装方法の検討です。現状遅いと言っているくらいですから、最も簡易な方法で実装されています。言わずもがな (1) で現状実装されているアプリがあります。

速度としては概ね以下のような傾向があります。

(4)SQL *Loader ≒ (3)バルクインサート処理　>>　(2)マルチテーブルインサート　>>　(1)単純 insert ループ

なお、既に (1) で実装されている場合、アプリケーションの改修コストとしては

(2)マルチテーブルインサート　>>　(3)バルクインサート処理　≧　(4)SQL *Loader

程度のコストがかかると思います。（少なくとも僕の試算上は・・・）
(1) → (2) であればループ処理を 100 単位でまとめて insert 文をマルチテーブルインサート文に修正する程度のコストです。
(1) → (3) であれば PL/SQL を定義して PL/SQL のインタフェースにあわせてデータを渡してエラーハンドリングを別途書き直して・・・と修正項目は山盛りです。
(2) → (4) であれば SQL *Loader のコントロールファイルと import 用 csv を自動生成して　ｓystem コマンドで sqlldr を起動してエラーハンドリングは sqlldr のログファイルを解析して・・・といったん perl のプロセスから離れるため更にやっかいです。


というわけで、スクラッチで作るわけでなく高速化のためのパッチと考えるならば、(2) のマルチテーブルインサートへの移行が比較的低コスト（テスト含め）で移行可能です。以下、簡単なベンチマークを書いて疑似表で計測したものをさらしておきます。

結論から書いておくと、
(1) 単純 insert 方式より (2) マルチテーブルインサートは最大１０倍程度の高速化が見込める。
(2) マルチテーブルインサートより (4) SQL *Loader は最大５倍程度の高速化が見込める。

です。では以下ベンチマーク・スクリプトです。あくまでパフォーマンス指標をだすための疑似表に対するパフォーマンスなので実際は問題となる表に対して計測しなくてはならないのですが本番環境に対してベンチマークができるわけもなく開発環境にて同じデータ数をそろえたりうんうんと意外と面倒なのも事実なので１つの参考値までにどうぞ。

(1) 単純 insert 文のループと (2) マルチテーブルインサートのパフォーマンス差の計測

マルチテーブルインサートの方が高速なのは自明です。では一度にいくつのデータを登録する際が最もパフォーマンスがでるかのベンチマークも同時にとってみました。全てバインド変数を用いて SQL を実行するわけですが、バインド変数は1000 列以上をサポートしていない（つまりバインド変数は 1000 以下ってこと）ので、そこら辺が実際にプログラム改修する際の隠れたポイントでもあります。

まずは疑似表の作成から。いつものセッションデータみたいのを定義します。

CREATE TABLE sess3 (
  ID             varchar(72) NOT NULL,
  DATA           VARCHAR2(4000),
  TIMESTAMP      DATE,
  PRIMARY KEY (ID)
);

つぎにスクリプト。ID にユニークキー。 DATA に 100byte の文字列。 TIMESTAMP は sysdate で。 

use DBI;
use DBD::Oracle qw(:ora_types);
use Benchmark qw/cmpthese timethese/;
timethese(
    1,
    {
        'oralce::insert 0001' => '&_insert(   1, 100, 10000)',
        'oralce::insert 0002' => '&_insert(   2, 100, 10000)',
        'oralce::insert 0005' => '&_insert(   5, 100, 10000)',
        'oralce::insert 0010' => '&_insert(  10, 100, 10000)',
        'oralce::insert 0050' => '&_insert(  50, 100, 10000)',
        'oralce::insert 0100' => '&_insert( 100, 100, 10000)',
        'oralce::insert 0200' => '&_insert( 200, 100, 10000)',
        'oralce::insert 0300' => '&_insert( 300, 100, 10000)',
        'oralce::insert 0400' => '&_insert( 400, 100, 10000)',
    }
);
my $oracle = DBI->connect( 'dbi:Oracle:testdb', 'test', 'pw123' );
sub _insert {
    my ($mode, $size, $loop) = @_;
    $size = 4000 if ( $size > 4000 );
    my $dbh = DBI->connect( 'dbi:Oracle:testdb', 'test', 'pw123' );
    $dbh->do('truncate table sess3');
    my @randstr = map { $_ } ('a'..'z',0..9);
    my $data = '';
    $data .= $randstr[int(rand(scalar @randstr))] foreach(1..$size);

    ## 単純 insert
    if ( $mode == 1 ) {
        my $sql = 'INSERT INTO sess3 VALUES (?,?,sysdate)';
        foreach ( 1 .. $loop ) {
            my $sth = $dbh->prepare($sql); $sth->execute( $_, $data ); $sth->finish();
        }
    ## マルチテーブル insert
    } else {
        my @bind;
        my $j = 0;
        foreach ( my $i = 1; $i <= $loop; $i++ ) {
            push @bind, ( $i, $data );
            $j ++;
            if ( $j == $mode || $i == $loop ) {
                my @bulksql;
                push @bulksql, 'INSERT ALL';
                for ( 1 .. (scalar @bind)/2 ) { push @bulksql, qq{INTO sess3 VALUES (?,?,sysdate)}; }
                push @bulksql, 'SELECT * FROM DUAL';
                my $sql = join( "\n", @bulksql ); my $sth = $dbh->prepare($sql);
                $sth->execute( @bind ); $sth->finish();
                @bind = (); $j = 0;
            }
        }
    }
    $dbh->disconnect;
}

ベンチマーク結果です。Oracle 10.2.0.1／Intel(R) Xeon CPU 3.40GHz × 2／mem 8GB　環境です。その他の環境でも同じ傾向でした。マルチテーブルインサートは同時登録が多いほど高速なわけではなく、概ね５０〜１００単位での登録が高速という結果になりました。 

Benchmark: timing 1 iterations of oralce::insert 0001, oralce::insert 0002, oralce::insert 0005, oralce::insert 0010, oralce::insert 0050, oralce::insert 0100, oralce::insert 0200, oralce::insert 0300, oralce::insert 0400...
oralce::insert 0001: 20 wallclock secs ( 1.07 usr +  0.03 sys =  1.10 CPU) @  0.91/s (n=1)
oralce::insert 0002: 13 wallclock secs ( 0.76 usr +  0.05 sys =  0.81 CPU) @  1.23/s (n=1)
oralce::insert 0005:  6 wallclock secs ( 0.38 usr +  0.00 sys =  0.38 CPU) @  2.63/s (n=1)
oralce::insert 0010:  4 wallclock secs ( 0.30 usr +  0.01 sys =  0.31 CPU) @  3.23/s (n=1)
oralce::insert 0050:  3 wallclock secs ( 0.25 usr +  0.00 sys =  0.25 CPU) @  4.00/s (n=1)
oralce::insert 0100:  3 wallclock secs ( 0.27 usr +  0.00 sys =  0.27 CPU) @  3.70/s (n=1)
oralce::insert 0200:  3 wallclock secs ( 0.33 usr +  0.01 sys =  0.34 CPU) @  2.94/s (n=1)
oralce::insert 0300:  3 wallclock secs ( 0.41 usr +  0.01 sys =  0.42 CPU) @  2.38/s (n=1)
oralce::insert 0400:  3 wallclock secs ( 0.45 usr +  0.00 sys =  0.45 CPU) @  2.22/s (n=1)

(2) マルチテーブルインサートと (4) SQL *Loader のパフォーマンス差の計測

さて、次に気になるのは改修コストが大きいけど最速と思われる SQL *Loader を利用する方法とマルチテーブルインサートの比較です。スクリプトを書くとまた長くなるので、こちらは結果のみ。

こちらも先ほどと同じ環境で実行したところ、SQL *Loader はマルチテーブルインサートよりも６倍ほどはやいという結果になりました。１０万レコードを６秒で登録できました。mysql と比較するとまだまだ遅いけど oracle としてはかなり高速だと思います。 

Benchmark: timing 1 iterations of oralce::insert  sql, oralce::insert load...
oralce::insert  sql: 26 wallclock secs ( 2.50 usr +  0.05 sys =  2.55 CPU) @  0.39/s (n=1)
oralce::insert load:  6 wallclock secs ( 0.24 usr  0.09 sys +  0.29 cusr  0.03 csys =  0.65 CPU) @  3.03/s (n=1)


以下、参考になった文章です。

- スポンサーリンク -

関連する記事＆スポンサーリンク