パフォーマンステスト中に、Oracleのimportでいくつかのテーブルのみが安定して異様に遅いという現象を発見しました。dmpファイルのサイズやレコード件数で同じ程度の他のテーブルと比較した際に、数十倍単位で速いケースもありました。バージョンは「Oracle Database 10g Release 10.2.0.4.0 - 64bit Production」です。
遅いグループと速いグループの差異を調べたところ、遅いグループにはすべてTIMESTAMP型が含まれており、速いグループには含まれていないことが分かりました。半信半疑だったのですが、とりあえず以下の3タイプのテーブルを用意し比較してみました。
- T1：TIMESTAMP型のカラム1つだけのテーブル
- T2：DATE型のカラム1つだけのテーブル
- T3：VARCHAR2(30)型のカラム1つだけのテーブル
すべてのテーブルに主キーもインデックスもありません。データ作成にはPL/SQLでループで少しずつ時間を加算する簡単なプログラムを作成しました。T1用とT2用は変数の型以外は全く同じ、T3用はT1用とロジックは同じでinsert文の値の個所で「to_char(vLogTime, 'YYYY/MM/DD HH24:MI:SS.FF6')」と型変換をしている点のみ異なります。
カラムの型の違いに着目したexportとimport
このテーブルとデータを使ってexportとimportをそれぞれ行ったところ、以下のような結果が得られました。
※このページすべてに共通しますが、処理時間が1分以下だったものについては、2回もしくは3回の試行の平均値をとっています。
|
T1 |
T2 |
T3 |
データ作成時間
PL/SQLでinsert文のループ |
2分19秒42 |
2分10秒11 |
2分27秒56 |
| dmpファイルサイズ |
43M |
43M |
134M |
| export (DIRECT=Y) |
10秒 |
10秒 |
28秒 |
| import (drop) |
3164秒[52分44秒] |
10秒 |
19秒 |
| dba_segments上のサイズ(※1) |
72MB |
72MB |
176MB |
--(※1)
select OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES / 1024 / 1024 as "BYTES(MB)"
from dba_segments
where OWNER = 'スキーマ名'
;
特徴をまとめると
- データ作成時間はどれもほぼ一緒
- T1とT2のdmpファイルはバイト単位で完全に同じサイズだった
- T3のdmpファイルは他の3倍程度大きい
- T3のexport時間は他の3倍程度
- T3のimport時間はT2の2倍程度
- T1のimport時間はT2の300倍以上
exportでT3のdmpファイルサイズが他の3倍になるのは主に型の違いでしょう。DATE型は7バイト、T3は「YYYY/MM/DD HH24:MI:SS.FF6」で格納しており26バイトなので、盲目的に単純計算をすれば3.7倍程度になるはずですが、他の付加情報があるであろう事を考慮すれば3倍程度というのは特に疑問の発生しない範囲でしょう。T3のexport時間が他の3倍かかっているのは、dmpファイルサイズに比例していると考えれば特に疑問は発生しないでしょう。
T1のexportはT2と比べると300倍以上の処理時間がかかりました。型がDATEとTIMSPAMPと異なるだけでこれだけの差が発生しました。他に比べるとfeedbackの「.」が最初に表示されるまでに非常に時間がかかったため、何か間違っているのかと思いました。いまのところ原因はわかりませんが、TIMESTAMP型があるテーブルのimportは過激なまでに遅いことはわかりました。
exportのDIRECTオプション
過去の作業の経験から、exportのDIRECTオプションが意味があるのか疑問でした。今回のテストの100倍以上のサイズのデータの出し入れが必要な作業の際に、DIRECTオプションのアリとナシで処理速度を比較したのですが、DIRECTオプションがあってもなくてもほとんど変わらない、むしろあるほうがほんの少し遅い(誤差の範囲かも)という経験をしたことがありました。そのため、ついでにexportのオプションの「DIRECT=Y」と「DIRECT=N」の場合で差があるのか試してみました。
T2とT3については大きな変化は見られませんでした。強いて言うならばT2については「DIRECT=Y」のほうが少し速く、T3については「DIRECT=N」のほうが微妙に速いということが分かりました。顕著に差が出たのはT1。TIMESTAMP型があるテーブルのexportでは「DIRECT=N」だと劇的に遅いことが分かりました。あと、ファイルサイズが「DIRECT=Y」のほうがやや小さくなるという点も興味深かったです。
私が過去に作業をして、DIRECTオプションがあってもなくてもほとんど変わらないという経験はおそらくTIMESTAMP型のカラムを持たないテーブルだったのだと思います。
|
T1 |
T2 |
T3 |
| export (DIRECT=Y) |
10秒 |
10秒 |
28秒 |
| export (DIRECT=N) |
632秒 |
13秒 |
25秒 |
| dmpファイルサイズ |
53M |
53M |
144M |
| import (drop) |
3349秒[55分49秒] |
8秒 |
22秒 |
CSVのload
CSVのloadも試してみました。CSVファイルの作成はT2のデータから行い、3つのテーブルとも同じCSVファイルからloadを行いました。CSV作成時間は5分11秒、CSVファイルサイズは387Mでした。行数は当然500万行です。
|
T1 |
T2 |
T3 |
| CSVのload時間 (DIRECT=TRUE) |
22秒 |
22秒 |
22秒 |
loadは3テーブルともほぼ同じ時間でした。CSVファイルを作成するのが少し手間だったりファイルサイズが非常に大きくなるというデメリットはありますが、型に影響されず安定して速いという点は大きな魅力だと思います。
データ投入方法による比較
これまでに得られた処理時間情報を元に、データ投入という観点から比較してみました。
|
T1 |
T2 |
T3 |
| PL/SQLでinsert文のループ |
2分19秒42 |
2分10秒11 |
2分27秒56 |
| import (export時 DIRECT=Y) |
3164秒[52分44秒] |
10秒 |
19秒 |
| import (export時 DIRECT=N) |
3349秒[55分49秒] |
8秒 |
22秒 |
| CSVのload (DIRECT=TRUE) |
22秒 |
22秒 |
22秒 |
| CSVのload (DIRECT=FALSE) |
3分01秒 |
2分53秒 |
2分43秒 |
以下のことがわかりました。
- PL/SQLでinsert文のループは安定している(速いとも遅いともいえない程度)
- export時のDIRECTオプションはimport時にはほとんど影響を与えない
- importはTIMESTAMP型がなければ非常に速い
- importはTIMESTAMP型があると劇的に遅くなる
- DIRECTを指定したloadは型によらず安定して速い
- DIRECTを指定していないloadは安定して遅い
- DIRECTを指定していないloadは型によって多少のばらつきがあるように思える(上記の値は3回試行した平均値)
TIMESTAMPを含む場合は、import以外の方法を検討したほうがいいでしょう。
その他
ちなみに検索に関しては高速でした。インデックス無しなのに。初回で数秒程度、2回目以降は1秒未満。どのテーブルでも似た感じで、型の違いによる検索処理時間の差は有意と思える値を取得できなかったことと、他と共有して使用しているため勝手にセッションキャッシュをクリアできる環境でもなく、繰り返し実行することが難しいため値を取得していません。大差は無いようだ、くらいにとってもらっていいと思います。
余談
ついでに、VARCHAR2とCHARの速度の違いも調べてみました。
以下の条件でテーブルを作成しデータを投入しました。
- T4：VARCHAR2(30)
- T5：CHAR(30)
- 3文字から28文字のランダムな文字列を投入：dbms_random.string('x', dbms_random.VALUE(3, 28))
データの投入時間・検索・spoolの時間が以下です。
| 処理 |
T4 |
T5
|
| 500万件のデータ作成 |
5分39秒41 |
5分39秒72
|
| count※4回試行の平均 |
1.95秒 |
2.79秒
|
| select spoolあり※2回試行の平均 |
20.29秒 |
20.67秒
|
| select spoolなし※4回試行の平均 SET TRIMOUT ON |
17秒 |
18.5秒
|
結果として以下のことが分かりました。
- データ投入時間は変わらない
- countはCHARがだいぶ遅い
- 全表検索はCHARが若干遅い
- 「SET TRIMOUT ON」をするとやや速くなる
