Oracle - SQL*Loader のパフォーマンス考察

Oracle で大量の insert を高速に処理する方法の検討　ですでに検証済みですが、Oracle での最速の insert を求めるなら SQL*Loader を使うことです。もちろんテーブルの状態によって計測値はかわりますが、以前の検証結果では SQL*Loader はマルチテーブルインサートよりも６倍ほどはやいという結果でした。

というわけで、オンライン系はともかく、バッチ系の処理での大量 insert 処理に SQL*Loader を使っていなければ、今すぐ SQL*Loader の導入を検討するべきでしょう。

ってことを自分自身わかりつつ、つい最近まで時間がとれていなかったのですが、最近は時間ができたのでバッチ処理の見直しをしているところです。SQL*Load にもいろいろ速度と制約があるので、その辺をまとめていきたいと思います。

- スポンサーリンク -

SQL*Loader ってなんですか？　って方へ

まずは、SQL*Loader ってなんじゃらほい？って方はまずは Oracle のマニュアルページをご一読下さい。逆に言うと、これ以上の情報は書籍でも見たことがありませんので必要十分な情報がココだけで手にはいるはずです。

マニュアル長ぇーよって方は、こちらの方が実は読みやすいです。情報量は落ちますが。

SQL*Loaderの機能概要まとめ

SQL*Loader とは外部ファイルのデータを Oracle データベースの表にロードするためのツールです。強力なデータ解析エンジンによってあらゆるデータ形式のデータ・ファイルに対応できます。SQL*Loader をでは以下の要件が可能です。

ネットワークを介したデータのロード。つまり、SQL*Loader サーバーを実行しているシステムとは異なるシステム上で、SQL*Loader クライアントを実行できることを意味しています。
同一のロード・セッションでの複数のデータ・ファイルからのデータのロード。
同一のロード・セッションでの複数の表へのデータのロード。
データのキャラクタ・セットの指定。
ロード・データの選択（レコード値に基づいたロード）。
ロード前の、SQL関数を使用したデータ処理。
指定した列に対する、一意の順序キーの生成。
オペレーティング・システムのファイル・システムを使用したデータ・ファイルへのアクセス。
ディスク、テープまたはNamed Pipeからのデータのロード。
高度なエラー・レポートの生成による、トラブルシューティングの支援。
複合オブジェクト・リレーショナル・データの任意のロード。
セカンダリ・データ・ファイル（SDF）を使用した、LOBおよびコレクションのロード。
従来型パス・ロードまたはダイレクト・パス・ロードの使用。従来型パス・ロードでは高い柔軟性を、ダイレクト・パス・ロードでは優れたロード・パフォーマンスを。

さて、自分への備忘録含めて書いているので随分を前置きが長くなってしまったわけですが、いよいよ検証結果についてまとめていきます。

とある条件下での SQL*Loader パフォーマンス検証

前提条件

データベースは初期ロード（つまり何も入っていない空のテーブルに対してのインサート）
レコード数は焼く１６０万レコード（※実はこのレコード数がいろいろな場所に大きな影響あり）

テーブル構成は

CREATE TABLE user (
    id                          VARCHAR(50) NOT NULL,
    mailaddr                    VARCHAR(50),
    zipcode1                    VARCHAR(3),
    zipcode2                    VARCHAR(4),
    pref                        VARCHAR(2),
    address                     VARCHAR(500),
    borndate                    DATE,
    sex                         VARCHAR(2),
    on_created                  DATE DEFAULT SYSDATE,
    on_updated                  DATE,
    on_deleted                  DATE,
    magiccode                   VARCHAR(50)
);
ALTER TABLE user ADD ( CONSTRAINT PK_user PRIMARY KEY (ID) );
CREATE INDEX IDX_user_MAIL_ADDR ON user (mailaddr);

では、以下検証結果です。以下の検証を目的としています。どう読み取るかはおまかせします。ココではとある１例の結果のみ掲載で。

1.　ダイレクトモードと従来型の速度差の検証
2.　従来型における commit 頻度による速度差の検証
3.　パラレルクエリーの有効性

OPTIONS( DIRECT = TRUE, PARALLEL = FALSE, ERRORS = -1, ROWS = -1 )

ログファイルから必要ヶ所を抜粋

----------------------------------------
SQL*Loader: Release 10.2.0.1.0 - Production on 水 5月 7 16:11:04 2008

ロード数: ALL
スキップ数: 0
許容エラー数: ALL
継続文字:    指定なし
使用パス:      ダイレクト

表userの次の索引が処理されました:
索引RES.IDX_user_MAIL_ADDRに1610658キーが正常にロードされました。
索引RES.PK_userに1610658キーが正常にロードされました。

表user:
  1610658行のロードに成功しました。

実行時間:        00: 01: 08.62
CPU時間 :        00: 00: 10.28
----------------------------------------

OPTIONS( DIRECT = TRUE, PARALLEL = TRUE, ERRORS = -1, ROWS = -1 )

実行エラー発生！

----------------------------------------
SQL*Loader: Release 10.2.0.1.0 - Production on 水 5月 7 16:43:18 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL*Loader-281: 警告: パラレル・モードのROWSパラメータは無視されました。
SQL*Loader-951: once/load初期化呼出しでエラーが発生しました
ORA-26002: 表RES.userに索引が定義されています。
----------------------------------------

※エラーの理由は以下の通り
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19211-01/ldr_modes.html#7035

パラレル・ダイレクト・パス・ロードの制限
パラレル・ダイレクト・パス・ロードには次の制限があります。
ローカル索引もグローバル索引もロードによってメンテナンスできません。
参照整合性およびCHECK制約は使用禁止にする必要があります。
トリガーは使用禁止にする必要があります。
行は追加（APPEND）のみできます。REPLACE、TRUNCATEおよびINSERTは使用できません（これは、個別のロードによって整合性がとられないためです）。パラレル・ロードの前に表を切り捨てる必要がある場合は、手動で行ってください。
1つのパーティションのパラレル・ダイレクト・パス・ロードを行う場合は、まず、データをパーティション化してください（そうしない場合は、パーティション不一致によるレコード拒否のオーバーへッドのため、ロード速度が遅くなります）。

OPTIONS( DIRECT = FALSE, ERRORS = -1, READSIZE = 20000000, BINDSIZE = 20000000, ROWS = 100 )

ログファイルから必要ヶ所を抜粋

----------------------------------------
SQL*Loader: Release 10.2.0.1.0 - Production on 水 5月 7 19:32:47 2008

ロード数: ALL
スキップ数: 0
許容エラー数: ALL
バインド配列:    100行、最大20000000バイト
継続文字:    指定なし
使用パス:      従来型

表user:
  1610658行のロードに成功しました。

実行時間:        00: 13: 17.58
CPU時間 :        00: 00: 27.88
----------------------------------------

※従来モードでの commit は概ねこんな感じで発生する。

・ホスト配列域(BINDSIZE)が埋まるとcommit
・READSIZEにしたがって読み込まれて内容が尽きるとcommit
・中断したら(ERRORSとかで)commit

なおログからは見えない部分ではあるが、索引がついているテーブルに実施してしまったためインデックスの更新が徐々に遅くなるのが目に見てわかる。
つまり、運用フェーズに入ってから、テーブルにデータを追加していくようなパターンを考えると、初期インサート時よりもパフォーマンスが悪いことは容易に想像できる。

OPTIONS( DIRECT = FALSE, PARALLEL = TRUE, ERRORS = -1, READSIZE = 20000000, BINDSIZE = 20000000, ROWS = 100 )

ログファイルから必要ヶ所を抜粋

----------------------------------------
SQL*Loader: Release 10.2.0.1.0 - Production on 水 5月 7 19:51:32 2008

ロード数: ALL
スキップ数: 0
許容エラー数: ALL
バインド配列:    100行、最大20000000バイト
継続文字:    指定なし
使用パス:      従来型

表user:
  1610658行のロードに成功しました。

実行時間:        00: 13: 18.56
CPU時間 :        00: 00: 28.33
----------------------------------------

※パラレルの効果は全く見られなかった。むしろインデックスの更新の遅延でその効果が全く見えなくなっていると言えるかも。

OPTIONS( DIRECT = FALSE, ERRORS = -1, READSIZE = 20000000, BINDSIZE = 20000000, ROWS = 1000)

ログファイルから必要ヶ所を抜粋

----------------------------------------
SQL*Loader: Release 10.2.0.1.0 - Production on 水 5月 7 20:07:18 2008

ロード数: ALL
スキップ数: 0
許容エラー数: ALL
バインド配列:    1000行、最大20000000バイト
継続文字:    指定なし
使用パス:      従来型

表user:
  1610658行のロードに成功しました。

実行時間:        00: 13: 57.10
CPU時間 :        00: 00: 27.59
----------------------------------------

※初めの方は ROWS=100 モードより高速に動作している感じ。でもインデックス更新が・・・以下略。

OPTIONS( DIRECT = FALSE, ERRORS = -1, READSIZE = 20000000, BINDSIZE = 20000000, ROWS = 4000 )

ログファイルから必要ヶ所を抜粋

----------------------------------------
SQL*Loader: Release 10.2.0.1.0 - Production on 木 5月 8 09:21:40 2008

ロード数: ALL
スキップ数: 0
許容エラー数: ALL
バインド配列:    4000行、最大20000000バイト
継続文字:    指定なし
使用パス:      従来型

表user:
  1610658行のロードに成功しました。

実行時間:        00: 14: 46.13
CPU時間 :        00: 00: 26.58
----------------------------------------

※初めの方は ROWS=100 モードより高速に動作している感じ。でもインデックス更新が・・・以下略。

というわけで、ロードするレコード数を20万レコードに減らして再度テストしてみる。

OPTIONS( DIRECT = FALSE, ERRORS = -1, READSIZE = 20000000, BINDSIZE = 20000000, ROWS = 4000, LOAD = 200000 )

ログファイルから必要ヶ所を抜粋

----------------------------------------
SQL*Loader: Release 10.2.0.1.0 - Production on 木 5月 8 10:54:06 2008

ロード数: 200000
スキップ数: 0
許容エラー数: ALL
バインド配列:    4000行、最大20000000バイト
継続文字:    指定なし
使用パス:      従来型

表user:
  200000行のロードに成功しました。

実行時間:        00: 00: 19.50
CPU時間 :        00: 00: 03.50
----------------------------------------

OPTIONS( DIRECT = FALSE, ERRORS = -1, READSIZE = 20000000, BINDSIZE = 20000000, ROWS = 1000, LOAD = 200000 )

ログファイルから必要ヶ所を抜粋

----------------------------------------
SQL*Loader: Release 10.2.0.1.0 - Production on 木 5月 8 10:54:06 2008

ロード数: 200000
スキップ数: 0
許容エラー数: ALL
バインド配列:    1000行、最大20000000バイト
継続文字:    指定なし
使用パス:      従来型

表user:
  200000行のロードに成功しました。

実行時間:        00: 00: 23.86
CPU時間 :        00: 00: 03.26
----------------------------------------

OPTIONS( DIRECT = FALSE, ERRORS = -1, READSIZE = 20000000, BINDSIZE = 20000000, ROWS = 100, LOAD = 200000 )

ログファイルから必要ヶ所を抜粋

----------------------------------------
SQL*Loader: Release 10.2.0.1.0 - Production on 木 5月 8 10:57:56 2008

ロード数: 200000
スキップ数: 0
許容エラー数: ALL
バインド配列:    100行、最大20000000バイト
継続文字:    指定なし
使用パス:      従来型

表user:
  200000行のロードに成功しました。

実行時間:        00: 00: 27.89
CPU時間 :        00: 00: 03.36
----------------------------------------

以上おしまい。ちなみに、Perl から SQL*Loader を操るには、

Oracle::Loader
Oracle::SQLLoader

あたりがあります。後者はテストスクリプトが入っているのでわかりやすいけど、前者はちょっとアレ。でもできることは前者の方が多いっぽい。この辺はまた別エントリで。

- スポンサーリンク -

Oracle - SQL*Loader のパフォーマンス考察

SQL*Loader ってなんですか？　って方へ

SQL*Loaderの機能概要まとめ

とある条件下での SQL*Loader パフォーマンス検証

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

コメントやシェアをお願いします！

Oracle - SQL*Loader のパフォーマンス考察

SQL*Loader ってなんですか？ って方へ

SQL*Loaderの機能概要まとめ

とある条件下での SQL*Loader パフォーマンス検証

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

コメントやシェアをお願いします！

少し前に書いた記事

タグクラウド

お問い合わせ・広告掲載

SQL*Loader ってなんですか？　って方へ