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

データベース技術

18年前に書いた記事です。製品やサービスの情報は公開当時のものです。

目次(6項目)
  1. 大量 insert の4つの方法
  2. 速度と改修コストの比較
  3. 先に結論
  4. (1) 単純 insert 文のループと (2) マルチテーブルインサートのパフォーマンス差の計測
  5. (2) マルチテーブルインサートと (4) SQL*Loader のパフォーマンス差の計測
  6. 参考になった文章

Oracle で大量の insert を高速に処理する方法の検討のイメージ(挿絵)

久々に Oracle ネタです。以前 セッション管理に向いているデータベースは MySQL ? Oracle ? というエントリを書いたのですが、それとは別に insert の処理をどうしても高速化する必要に迫られました。

今回高速化すべき要件は、同一テーブルに大量のデータを一気に登録する作業です。いわゆる管理系業務で良くある csv のインポート的な機能に相当します。

大量 insert の4つの方法

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

  1. アプリ側から csv のデータを読み込んで for 文で件数分ループして insert する
  2. マルチテーブルインサート機能を用いて、1つの 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 を自動生成して、system コマンドで sqlldr を起動して、エラーハンドリングは sqlldr のログファイルを解析して……と、いったん perl のプロセスから離れるため更にやっかいです。

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

先に結論

結論から書いておくと、

  • (1) 単純 insert 方式より (2) マルチテーブルインサートは最大10倍程度の高速化が見込める。
  • (2) マルチテーブルインサートより (4) SQL*Loader は最大5倍程度の高速化が見込める。

です。では以下ベンチマーク・スクリプトです。

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

(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)

マルチテーブルインサートは同時登録が多いほど高速なわけではなく、概ね50〜100単位での登録が高速という結果になりました。

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

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

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)

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

参考になった文章

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

最終更新