Oracle Databaseユーティリティ11g リリース1（11.1）11 従来型パス・ロードおよびダイレクト・パス・ロードに、ダイレクト・パス・ロードのパフォーマンスの最適化という項目がある。OracleとSQL*Loaderの勉強ということで、ここに書いてある方法を試していくことにする。
やったこと
計測の基準
いくつかのオプションやらなんやらを付いた状態・付けない状態とで実行時間を比較していく。よって、何かしらの状態を基準に置いて、それとの比較をする、って感じです。また、特段の指定が無い限り下記のテーブル定義やSQL*Loaderの制御ファイルを使う、ということにします。
データを投入していくテーブルはこんな感じ。
CREATE TABLE DEST (COLUMN1 VARCHAR2(16) , CONSTRAINT DEST_PK PRIMARY KEY(COLUMN1));
SQL*Loaderの起動コマンドはこんな感じ。
C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlldr.exe kagamihoge/xxxx@XE2 CONTROL=index_data.ctl LOG=index_data.log BAD=index_data.bad
制御ファイルはこんな感じ。ダイレクトパスモードでロードするってのと、固定長ファイル形式、INSERTオプションを付けてテーブルを毎回DROP＆CREATEしています。
OPTIONS (DIRECT=TRUE) load data infile 'data_asc.dat' "fix 16" insert into table DEST (COLUMN1 CHAR(16))
入力データはこんな感じ。ゼロ埋め16バイトで数値が昇順に並んだものを百万件。
0000000000000001 0000000000000002 0000000000000003 （以下略。実際には改行無し）
これの実行時間はこんな感じ。以下、実行時間のところで「ほげほげ（基準）」または単に「（基準）」と書いた場合、下記の実行時間を指すことにします。
| （00秒00の形式） | 1 | 2 | 3 |
|---|---|---|---|
| （基準） | 09.44 | 08.00 | 07.45 |
高速ロードのための記憶域の事前割当て
高速ロードのための記憶域の事前割当て
SQL*Loaderでは、必要に応じて自動的に表にエクステントが追加されますが、これには時間がかかります。新しい表へ高速にロードするには、表の作成に必要なエクステントを事前に割り当ててください。Oracle Databaseユーティリティ11g リリース1（11.1）- 11 従来型パス・ロードおよびダイレクト・パス・ロード - ダイレクト・パス・ロードのパフォーマンスの最適化 より抜粋
表領域のパラメータなどを確認する。XEをデフォルトでインストールした状態から特に変更した覚えは無いんで、たぶんデフォルトの設定。
SELECT TABLESPACE_NAME, BLOCK_SIZE, INITIAL_EXTENT, EXTENT_MANAGEMENT, ALLOCATION_TYPE FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'USERS';
| TABLESPACE_NAME | BLOCK_SIZE | INITIAL_EXTENT | EXTENT_MANAGEMENT | ALLOCATION_TYPE |
|---|---|---|---|---|
| USERS | 8192 | 65536 | LOCAL | SYSTEM |
まず、INITIALは指定しないでテーブルを作成し、そこへデータを投入する。
CREATE TABLE DEST (COLUMN1 VARCHAR2(16) , CONSTRAINT DEST_PK PRIMARY KEY(COLUMN1));
そのあとUSER_SEGMENTSで使用サイズを確認する。
SELECT SEGMENT_NAME, BYTES, BLOCKS, EXTENTS FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('DEST', 'DEST_PK');
| SEGMENT_NAME | BYTES | BLOCKS | EXTENTS |
|---|---|---|---|
| DEST | 26214400 | 3200 | 40 |
| DEST_PK | 31457280 | 3840 | 45 |
上で確認したサイズをINITIALに指定してテーブルを作成する。まぁフツウはサイズ指定にこんな乱暴なことせずに計算式で見積もりするんだろうけど、そこは今回の目的でないんで手っ取り早い方法を取りました。
DROP TABLE DEST PURGE; CREATE TABLE DEST ( COLUMN1 VARCHAR2(16) , CONSTRAINT DEST_PK PRIMARY KEY(COLUMN1) USING INDEX STORAGE( INITIAL 31457280 ) ) STORAGE ( INITIAL 26214400 );
USER_SEGMENTSでエクステントが初期状態では何個になっているか確認する。
| SEGMENT_NAME | BYTES | BLOCKS | EXTENTS |
|---|---|---|---|
| DEST | 26214400 | 3200 | 4 |
| DEST_PK | 31457280 | 3840 | 9 |
INITIAL句指定前は、エクステントの数がテーブル40,インデックス45だったのが、4,9になっている。
SQL*Loaderのスクリプト実行後にエクステント数が変化していないことを確認する。
| SEGMENT_NAME | BYTES | BLOCKS | EXTENTS |
|---|---|---|---|
| DEST | 26214400 | 3200 | 4 |
| DEST_PK | 31457280 | 3840 | 9 |
で、実行時間の比較。
| （00秒00の形式） | 1 | 2 | 3 |
|---|---|---|---|
| INITIAL無し（基準） | 09.44 | 08.00 | 07.45 |
| INITIAL有り | 06.88 | 07.49 | 06.80 |
拡張される分のエクステントをあらかじめ確保しておいた方が、確かに速度的には優位になるようだ。ただ、このくらいのデータ量だとそこまで劇的な差が出てくるってわけでもないらしい。
オマケで、Oracleの例のリファレンスには書いてないけどPCTFREE 0にしてブロックI/O数減らしたらどうなるかを試してみる。
テーブルとインデックスにPCTFREE 0を設定する。
CREATE TABLE DEST ( COLUMN1 VARCHAR2(16) , CONSTRAINT DEST_PK PRIMARY KEY(COLUMN1) USING INDEX PCTFREE 0 ) PCTFREE 0 ;
ロード終了後のセグメントの状態。使用ブロック数が減ってるのが確認できる。
| SEGMENT_NAME | BYTES | BLOCKS | EXTENTS |
|---|---|---|---|
| DEST | 23068672 | 2816 | 37 |
| DEST_PK | 28311552 | 3456 | 42 |
で、実行時間の比較。
| （00秒00の形式） | 1 | 2 | 3 |
|---|---|---|---|
| なし（基準） | 09.44 | 08.00 | 07.45 |
| PCTFREE 0 | 08.96 | 06.79 | 07.78 |
ブロックの数がテーブル・インデックスともにそれぞれ約400ずつ減った分だけ速くなってる……かな？ といったところ。
INITIALとPCTFREE 0両方指定したときはどうなるかやってみる。
テーブル作成はこんな感じ。
CREATE TABLE DEST ( COLUMN1 VARCHAR2(16) , CONSTRAINT DEST_PK PRIMARY KEY(COLUMN1) USING INDEX PCTFREE 0 STORAGE( INITIAL 28311552 ) ) PCTFREE 0 STORAGE ( INITIAL 23068672 );
で、実行時間の比較。
| （00秒00の形式） | 1 | 2 | 3 |
|---|---|---|---|
| （基準） | 09.44 | 08.00 | 07.45 |
| INITIAL有り | 06.88 | 07.49 | 06.80 |
| PCTFREE 0 | 08.96 | 06.79 | 07.78 |
| INITIAL有り + PCTFREE 0 | 07.55 | 06.47 | 07.17 |
最大で3秒くらいは縮んでる。テーブルにデータ投入後、参照しかされないのか更新がされるのか・されるとしたらどの列がどのくらい、かによってこの辺のパラメータは付けるか付けないかが変わってくると思うのだけど。PCTFREE 0は事実上読み取り専用テーブルのときにしかやれないんでまぁ忘れてもいいとしても、INITIAL見積もっての指定はやれるならやった方が良さそうな感じがする。
高速索引付けのためのデータの事前ソート
高速索引付けのためのデータの事前ソート
索引付き列を基準にしてデータを事前ソートすると、ダイレクト・パス・ロードのパフォーマンスを改善できますOracle Databaseユーティリティ11g リリース1（11.1）- 11 従来型パス・ロードおよびダイレクト・パス・ロード - ダイレクト・パス・ロードのパフォーマンスの最適化 より抜粋
要するに、入力ファイルはあらかじめインデックス列の順序でソートしておいたほうがいいよ、とのこと。ダイレクトパスロードでは、すでにインデックスにデータがある場合、一時セグメントに元データをコピーしてきてロードデータとマージをする、てなことが書いてある。そうなると、ロードデータがあらかじめソートされていれば、一時セグメント上でのソート作業は不要あるいは最小限に抑えられ、結果的に速度も向上する。参考：Oracle Databaseユーティリティ11g リリース1（11.1）- 8 SQL*Loader制御ファイル・リファレンスのSINGLEROWオプションのあたり。
また、データが空かつ入力ファイルがあらかじめソートされていることが分かっていれば、上記の一時セグメントの作業はすっ飛ばせるので更に速くなる、とかなんとか書いてある。
テーブルを準備する。
CREATE TABLE DEST (COLUMN1 VARCHAR2(16) , CONSTRAINT DEST_PK PRIMARY KEY(COLUMN1));
SQL*Loaderの起動コマンドはこんな感じ。
C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlldr.exe kagamihoge/xxxx@XE2 CONTROL=index_data.ctl LOG=index_data.log BAD=index_data.bad
制御ファイルはこんな感じ。
OPTIONS (DIRECT=TRUE) load data infile 'data_asc.dat' "fix 16" insert into table DEST (COLUMN1 CHAR(16))
データがあらかじめソートされていることをSQL*Loaderに伝えるためのSORTED INDEXES句を指定した場合はこんな感じ。入力ファイルは昇順のものを使用する。
OPTIONS (DIRECT=TRUE) load data infile 'data_asc.dat' "fix 16" insert into table DEST SORTED INDEXES (DEST_PK) (COLUMN1 CHAR(16))
入力ファイルは3つ用意する。1.昇順、2.降順、3.ランダム。
昇順はこんな感じ。ゼロ埋め16バイトで数値が昇順に並んだものを百万件。
0000000000000001 0000000000000002 0000000000000003 （以下略。また、実際には改行無し）
降順はこんな感じ。
0000000001000000 0000000000999999 0000000000999998 （以下略。また、実際には改行無し）
ランダムはこんな感じ。0000001〜1000000がランダムかつ一意になってる。
0000000000510970 0000000000817400 0000000000694564 （以下略。また、実際には改行無し）
上記のランダム用データは下記のSQLで作成した。一度テーブルに0000001〜1000000を挿入したあと乱数でソート。
SELECT COLUMN1 FROM SRC ORDER BY DBMS_RANDOM.VALUE;
で、実行時間の比較。
| （00秒00の形式） | 1 | 2 | 3 |
|---|---|---|---|
| ASC（SORTED） | 03.45 | 03.47 | 03.51 |
| ASC（基準） | 09.44 | 08.00 | 07.45 |
| DESC | 09.77 | 09.40 | 10.55 |
| RAND | 10.98 | 11.94 | 11.49 |
SORTED INDEXES指定したときの早さが一目瞭然。やはり、元来ソートというのは重めな処理というのが分かる。ここではPKしかないテーブルなので鮮やかな差が出たが、現実的には様々なインデックスがはられているのでここまで劇的には差は出ないかもしれない。とはいっても、ソートしなくてもよい列があるか無いかは相当な差にはなると思うけれども。
昇順と降順とでは大きな差は出ないものの、昇順有利なことには変わりは無い。調べてないんで推測になるけど、必ず末尾にデータを入れる方が、必ず先頭にデータを入れるよりかは、幾分か有利という話なんだと思われる。
ランダムになるとかなり遅くなる。ほぼ毎回、配列の途中にデータを挿入することになるので、そのたびに挿入場所プラス1のデータを1つずつずらしていくことになるんで、遅くなるのは頷ける結果である。
データ・セーブの回数の削減
データ・セーブの回数の削減
ROWS値が小さいことが原因でデータ・セーブが頻繁に発生する場合、ダイレクト・パス・ロードのパフォーマンスは低下します。ROWS値が小さい場合、セーブ後に最後のデータ・ブロックには書込みが行われないため、データ・ブロック領域が無駄になります。Oracle Databaseユーティリティ11g リリース1（11.1）- 11 従来型パス・ロードおよびダイレクト・パス・ロード - ダイレクト・パス・ロードのパフォーマンスの最適化 より抜粋
ROWSパラメータは、従来型の場合はコミット間隔の意味になるが、ダイレクトパスの場合はセーブ間隔の意味になるので注意してくださいね、と書いてある。
というわけでROWSの値をあえて書いたときに何が起きるかをやってみる。ROWSの値を、10万〜100に変えていったときの動作を見る。
OPTIONS (DIRECT=TRUE, ROWS=100000) load data infile 'data_asc.dat' "fix 16" insert into table DEST (COLUMN1 CHAR(16))
んで、実行結果。
| （00秒00の形式） | 1 | 2 | 3 |
|---|---|---|---|
| なし（基準） | 09.44 | 08.00 | 07.45 |
| ROWS=100000 | 08.47 | 08.20 | 07.04 |
| ROWS=10000 | 08.53 | 08.55 | 07.37 |
| ROWS=1000 | 09.11 | 08.30 | 08.93 |
| ROWS=100 | 38.68 | -*1 | - |
ぶっちゃけ、たかが十数秒しかかからない分量のデータだと実行時間に差が出てこない。Oracleのリファレンスにも書いてあるけど、数千万とかよほど巨大なデータでない限り、ダイレクトパスではROWSは付けない方がいいってことなんでしょう。勿論、実行時間のパフォーマンスを優先する限りにおいて、だろうけど。
ROWS=100にすると異様に遅くなるのは、大量にブロック領域を消費しているため。下記は、ROWS=100を実行後のUSER_SEGMENTの値。フツーにやって3200ブロックなんで、かなり余分に使っている。ブロックサイズは8192なんで、ROWS=100だと100*1行16バイト=1600とデータ・セーブ間隔までに消費するブロック内の領域がかなり少ない。ROWS=1000だと1000*16=16000なのでPCT_FREE=10を考慮してもかなりマシになる。
| SEGMENT_NAME | BYTES | BLOCKS | EXTENTS |
| DEST | 83886080 | 10240 | 81 |
| DEST_PK | 31457280 | 3840 | 45 |
REDOログの最小限の使用
REDOログの最小限の使用
ダイレクト・ロードを大幅に高速化する1つの方法は、REDOログの使用を最小限に抑えることです。
・アーカイブの使用禁止 アーカイブ・モードをNOARCHIVELOGにする。
・SQL*LoaderのUNRECOVERABLE句の指定
・SQL NOLOGGINGパラメータの設定
Oracle Databaseユーティリティ11g リリース1（11.1）- 11 従来型パス・ロードおよびダイレクト・パス・ロード - ダイレクト・パス・ロードのパフォーマンスの最適化 より抜粋
アーカイブモードの変更以外を試す。
UNRECOVERABLE句を使用はこんな感じ。
OPTIONS (DIRECT=TRUE) UNRECOVERABLE load data infile 'data_asc.dat' "fix 16" insert into table DEST (COLUMN1 CHAR(16))
NOLOGGINGはこんな感じに、テーブル作ったあとALTER TABLE|INDEXする。
ALTER TABLE DEST NOLOGGING; ALTER INDEX DEST_PK NOLOGGING;
んで、実行結果。
| （00秒00の形式） | 1 | 2 | 3 |
|---|---|---|---|
| なし（基準） | 09.44 | 08.00 | 07.45 |
| UNRECOVERABLE | 07.35 | 08.10 | 07.35 |
| NOLOGGING | 08.18 | 07.36 | 08.24 |
| NOLOGGING + UNRECOVERABLE | 08.42 | 08.08 | 07.23 |
若干速くなるかぁー？ といった感じ。UNRECOVERABLEとNOLOGGING両方やらないとダメなのか……？とか思ったがそんなことはなかった。前やったときと同じく0.5秒くらいは違うかなぁといったぐらい。せいぜい十秒で終わるデータ量だと、そもそもプラスマイナス1,2秒は変動する。そのため、REDOログ抑制の効果が出てるのかどうか、この実験からでは有意な何かを読み取ることは難しい。
列配列の行数およびストリーム・バッファ・サイズの指定
列配列の行数およびストリーム・バッファ・サイズの指定
STREAMSIZEパラメータで、クライアントからサーバーへ送ったデータのストリーム・サイズ（バイト単位）を指定します。
列配列の行数の値を指定するには、COLUMNARRAYROWSパラメータを使用します。Oracle Databaseユーティリティ11g リリース1（11.1）- 11 従来型パス・ロードおよびダイレクト・パス・ロード - ダイレクト・パス・ロードのパフォーマンスの最適化 より抜粋
とりあえず、各パラメータを適当に変えてやってみる。
まず、各パラメータのデフォルト値はウチの環境ではこんな感じ。
| STREAMSIZE | 256000 |
| COLUMNARRAYROWS | 5000 |
| READSIZE | 1048576 |
SQL*Loaderの制御ファイルはこんな感じ。
OPTIONS (DIRECT=TRUE, READSIZE=16000000, COLUMNARRAYROWS=100000, STREAMSIZE=512000) load data infile 'data_asc.dat' "fix 16" insert into table DEST (COLUMN1 CHAR(16))
んで、実行結果。それぞれの実行結果は、そのパラメータだけをOPTIONSに指定して実行しています。
| （00秒00の形式） | 1 | 2 | 3 |
|---|---|---|---|
| なし（基準） | 09.44 | 08.00 | 07.45 |
| READSIZE=16000000 | 08.05 | 08.85 | 07.40 |
| COLUMNARRAYROWS=1000 | 08.54 | 07.54 | 08.53 |
| COLUMNARRAYROWS=10000 | 07.38 | 07.94 | 07.42 |
| COLUMNARRAYROWS=100000 | 08.58 | 07.26 | 08.66 |
| STREAMSIZE=512000 | 07.96 | 08.18 | 07.40 |
| オマケ | 07.22 | 08.41 | 07.49 |
「オマケ」は、READSIZE=16000000, COLUMNARRAYROWS=100000, STREAMSIZE=512000で実行したときの実行結果。
入力ファイルがせいぜい16MB（16バイト×100万）だと、このあたりのパラメータは変更したところで然程意味が無いのだと思われる。Oracleのリファレンスにも書いてあるけど、入力ファイルの大きさ・列の型やサイズ・クライアントとサーバマシンのスペック・OSとファイルシステムまわりのパラメータ・ネットワーク帯域などなどによって変わるのだろう。あと、マルチコア時にパラレルにロードできるかどうかも大きい、とも書いてある。
日付キャッシュの値の指定
これはめんどいんでパス。
MULTITHREADING
MULTITHREADING
デフォルト: 複数CPUシステムではtrueで、単一CPUシステムではfalse。
デフォルトでは、マルチスレッド・オプションは常にtrueに設定され、複数CPUシステム上で使用可能です。この場合の複数CPUシステムの定義は、2つ以上のCPUを持つ単一システムです。
単一CPUシステムでは、マルチスレッドはデフォルトでfalseに設定されています。
（中略）
マルチスレッド機能は、オペレーティング・システムに依存します。すべてのオペレーティング・システムがマルチスレッドをサポートしているわけではありません。
Oracle Databaseユーティリティ11g リリース1（11.1）7 SQL*Loaderコマンドライン・リファレンス より抜粋
とまぁそんな感じで、マシンとOSが対応していないとtrueにしても意味が無いパラメータですよ、と書いてある。
実際にやってみる。制御ファイルで下記のようにMULTITHREADINGをtrueとfalseにしたときとで実行時間を比較してみる。
OPTIONS (DIRECT=TRUE, MULTITHREADING=false) load data infile 'data_asc.dat' "fix 16" insert into table DEST (COLUMN1 CHAR(16))
んで、実行結果。
| （00秒00の形式） | 1 | 2 | 3 |
|---|---|---|---|
| true | 08.09 | 08.08 | 08.71 |
| false | 08.13 | 09.87 | 07.42 |
俺の手元の環境はどうみても複数CPUシステムではないので、何も変わらないことが確認できる。
SKIP_INDEX_MAINTENANCE
SKIP_INDEX_MAINTENANCE
デフォルト: false ダイレクト・パス・ロードの索引メンテナンスを停止します。
Oracle Databaseユーティリティ11g リリース1（11.1）7 SQL*Loaderコマンドライン・リファレンス より抜粋
このパラメータにtrueを設定することで、ロード時にはインデックスを作成しないようにできる。それによってロードの時間そのものを減らすことが出来る。あくまでも、ロードの時間そのものだけ、である。
制御ファイルはこんな感じ。
OPTIONS (DIRECT=TRUE, SKIP_INDEX_MAINTENANCE=true) load data infile 'data_asc.dat' "fix 16" insert into table DEST (COLUMN1 CHAR(16))
インデックスのSTATUSがUNUSABLEになるので、そのインデックスを使うためには再構築が必要。
alter index DEST_PK rebuild;
んで、実行結果。
| （00秒00の形式） | 1 | 2 | 3 |
|---|---|---|---|
| false（基準） | 09.44 | 08.00 | 07.45 |
| true | 02.62 | 01.50 | 01.60 |
| rebuild | 09.37 | 08.17 | 09.15 |
| true + rebuild | 11.99 | 09.67 | 10.75 |
インデックス構築のコストが無くなるので、当然ロード時間そのものは極端に短くなる。しかし、インデックス構築の時間と合計すると、むしろ数秒ほど遅くなる結果となった。
入力データをロードしてテーブルに入れつつインデックスを作るとき（false）と、入力データをテーブルに入れ終わってからインデックスを再構築する（true）のとでは、前者は全データアクセスが1度で済むが、後者は2度しなければならない。前者はSORTED INDEXESで更に高速化の余地があるが、後者はSQL*Loaderより後の話になってしまうという点も見逃せない。
ただし、空のテーブルへの入力か・行数の多いテーブルへの少数データ入力か、多数のデータ入力か、等によって状況は変わってくる。カギとなるのは、ダイレクトパスロードの場合は基本的に既存インデックスとのマージの動作になる、という点。なので、既存インデックスを丸ごと一時領域にコピーしてロードデータとマージするのと、インデックス作成はスキップしてあとで再構築するのと、どちらがお得なのか？ という話になる。これは状況により何が有利かは変わってくるし、その辺試すのはめんどくさいので、今回はやらない。
ぜんぶやる
ここまで来たら「この環境とこの入力データでは」効果が見られた設定をすべて指定したときどうなるかやってみたくなるのが人情ってものです。
設定項目サマリ。
- INITIALの設定
- PCTFREE 0の設定
- 入力データの事前ソート
- SORTED INDEXESの指定
- REDOログの抑制（NOLOGGING）
CREATE TABLE DEST ( COLUMN1 VARCHAR2(16) , CONSTRAINT DEST_PK PRIMARY KEY(COLUMN1) USING INDEX PCTFREE 0 STORAGE( INITIAL 28311552 ) ) PCTFREE 0 STORAGE ( INITIAL 23068672 ); ALTER TABLE DEST NOLOGGING; ALTER INDEX DEST_PK NOLOGGING;
OPTIONS (DIRECT=TRUE) UNRECOVERABLE load data infile 'data_asc.dat' "fix 16" insert into table DEST SORTED INDEXES (DEST_PK) (COLUMN1 CHAR(16))
んで、実行結果。
| （00秒00の形式） | 1 | 2 | 3 |
|---|---|---|---|
| （基準） | 09.44 | 08.00 | 07.45 |
| ぜんぶ | 02.64 | 02.71 | 02.64 |
SKIP_INDEX_MAINTENANCE=true時の実行時間に迫る勢いじゃないっすか。
*1:一回しか試行していません
