概要
シバタツ流！ DWHチューニングの極意 第4回 SQL書き換えテクニックによると、DWHにおけるUPDATEをダイレクトパスインサートを用いたINSERT /*+ APPNED */ INTO ... SELECT ...に置き換えることで高速化を見込める、というテクニックが紹介されている。今回はこれを実際に試し、statspackのレポートを取得して、なぜ高速化が見込めるかを考える。また、同じ結果となる別のUPDATEとも比較を行い、OracleのUPDATEについて考える。
やること
やることそのものは参照元と変わらないが、このエントリでは実行時間にフォーカスを置く。その点を強調して物事を単純化するため、いくつか手抜きをする。
まず、UPDATEは全件更新にする。ただ、これだとUPDATEをSELECT CASEで置換するという重要な点が抜け落ちてしまう。そのため、UPDATE置き換えのエッセンスについては参照元を見て頂きたい。
実行時間にフォーカスをおくので、全件更新するパターンを何種類か用意して比較を行う。それらの実行時間とstatspackレポートを取得し、比較することでなぜ実行時間に差が生じるのか、なぜUPDATEのINSERT SELECTが有効なのか、を見ていく。
環境
- DB
- ツール
ソースコード
準備
更新対象のテストテーブルを作り100万件データを入れる。テーブルには単に一列だけでインデックスはない。速度計測の際には以下のSQLを毎回実行し、テーブルの再作成およびデータの挿入、バッファキャッシュのクリアを行う。
DROP TABLE tests PURGE; CREATE TABLE tests (column1 VARCHAR2(20)); INSERT /*+ APPEND */ INTO tests(column1) SELECT '01234567890123456789' FROM (SELECT ROWNUM FROM all_catalog WHERE ROWNUM <= 1000), (SELECT ROWNUM FROM all_catalog WHERE ROWNUM <= 1000) ; COMMIT; ALTER SYSTEM FLUSH BUFFER_CACHE;
全件更新の各パターンのSQL
以下はinsert selectのパターン。参照元を見て作成したもので、より単純化させている。insert selectで更新後の行となった新テーブルを作成してリネーム。なお、テーブルは全部同じ値で全件更新する。
CREATE TABLE tests_new (column1 VARCHAR2(20)); INSERT /*+ APPNED */ INTO tests_new(column1) SELECT '99999999990000000000' FROM tests; COMMIT; RENAME tests TO tests_old; RENAME tests_new TO tests; DROP TABLE kagamihoge.tests_old PURGE;
以下は単発updateのパターン。
UPDATE tests SET column1 = '99999999990000000000'; COMMIT;
以下はloop updateのパターン。rowidで1件ずつupdateしているが、実際にはユニークな値を基にすると思われる。
CREATE OR REPLACE PROCEDURE LOOP_UPDATE AS BEGIN FOR vRec IN (SELECT COLUMN1, ROWID P_ROWID FROM TESTS) LOOP UPDATE TESTS SET COLUMN1 = '99999999990000000000' WHERE ROWID = vRec.P_ROWID; END LOOP; COMMIT; END LOOP_UPDATE;
以下はcase updateのパターン。詳細は後述。
CREATE OR REPLACE PROCEDURE LOOP_CASE_UPDATE AS before_rowid rowid; BEGIN before_rowid := NULL; FOR vRec IN (SELECT COLUMN1, ROWID P_ROWID FROM TESTS ) LOOP IF (before_rowid is not NULL) THEN UPDATE TESTS SET column1 = CASE WHEN rowid = before_rowid THEN '99999999990000000000' WHEN rowid = vRec.P_ROWID THEN '99999999990000000001' ELSE NULL END WHERE rowid IN (before_rowid, vRec.P_ROWID); before_rowid := NULL; ELSE before_rowid := vRec.P_ROWID; END IF; END LOOP; IF (before_rowid is not NULL) THEN UPDATE TESTS SET COLUMN1 = '99999999990000000002' WHERE ROWID = before_rowid; END IF; COMMIT; END LOOP_CASE_UPDATE;
結果と考察とか
実行時間
| insert select | update | loop update | case update |
|---|---|---|---|
| 1.928 | 40.906 | 82.806 | 69.358 |
| 2.395 | 30.896 | 80.386 | 69.064 |
| 2.222 | 29.511 | 89.902 | 68.984 |
insert selectが圧倒的に早く、それからグッと遅く単発update、更にその2倍遅いloop updateで、case updateにするとややマシになる、といった具合。
考察とか
参照元にあるように、広範囲をUPDATEする場合はバッファキャッシュへ逐一読み取りするのがオーバーヘッドになるため、バッファキャッシュを介さないダイレクトパスインサートへ置き換えることが有効となりうる、との記述がある。今回の実行時間もそうなっており、単発UPDATEに比べると格段にinsert selectは早い。
この速度差の原因は何か。答えは上記の通りバッファキャッシュを介さないから、になるが、statspackを見てもう少し詳しく原因を考える。
insert selectとupdate
statspack（それぞれinsert_select.lstとupdate.lst）でポイントになると思われる部分を抜粋する。
| Statistic | insert select | update |
|---|---|---|
| db block gets | 37,120 | 1,035,893 |
| redo entries | 22,829 | 1,001,152 |
| redo size | 34,013,076 | 310,546,860 |
| DBWR undo block writes | 0 | 12,391 |
| DBWR checkpoints | 1 | 7 |
db block getsは今回の場合おおむねバッファキャッシュにブロックを読み込んだ回数に相当し、実際単発updateでは百万行と同じ数値になっている。しかし、insert selectはそれに比べれば格段に少ない。バッファキャッシュを介さない、という点がここに現れている。
ダイレクトパスインサートはREDOログの量も少なく抑えられるが、単発updateはそうもいかない。redo entriesは「REDOログ・バッファにコピーされるREDOエントリの回数」とマニュアルにあるので、おおむね百万回なのは納得が行く。同様に、redo sizeもおよそ10倍の差がついており、速度差の原因の一つはトランザクションの処理量にあることが伺える。
そうなると、チェックポイントの回数にも差が出てくる。チェックポイントは比較的負荷が高い処理なので、1回と7回とでは、速度差に影響を及ぼすことは間違いない。
通常のOracleのトランザクションでは読み取り一貫性などのためにUNDOセグメントがかかせない。単発updateの場合も当然その仕組みの中で実行されるので、DBWR undo block writesに幾らかの数値が現れる。しかし、DWHでの広範囲updateはそもそも同時実行が大して重要ではないので、その辺の機構が逆にボトルネックになりかねない。なので、それをバイパスするためのinsert selectによる置換だが、このケースの場合DBWR undo block writesはゼロ。これも実行時間に影響を与えると思われる。
ラッチも合わせて分析した方が良いのだけど、ややこしくて俺自身の知識もあやふやな部分が多いのでアレコレ述べるのはやめておく。
デメリットは何か。参照元にもあるように、ごく一瞬とはいえテーブルが見えなくなる瞬間がある。また、新規ブロックを割り当てるので、論理的には同一データであっても物理的には異なってくる*1。インデックスの作り直し*2も必要。基本的にはDWH系のみで、オンラインで使える技では無い。
loop updateとcase update
次に、ワーストケースであるloop updateと、それよりは幾分マシなcase updateを見ていく。
| Statistic | update | loop update | case update |
|---|---|---|---|
| db block changes | 2,018,565 | 2,026,110 | 2,019,353 |
| redo size | 310,546,860 | 311,137,684 | 310,634,944 |
| execute count | 244 | 1,001,953 | 501,476 |
db block changesやredo sizeといった辺りはほとんど変わらない。これは、どのやり方にしても結果的にアウトプットとなる量は変化しない、という意味。最終的な成果物の量は変わらないが、実行時間には差が生じる。つまり、過程に差がある、と判断できる。
過程の差はexecute countの、おおむねSQLの実行回数として現れている。参照元や今回のエントリで見たように、oracleのupdateは比較的重たい処理である。なので、UPDATEの回数が減ると、それに伴って各種の指標も減少し、実行時間の短縮に繋がる。
ではUPDATEの回数を減らすにはどうするか。もちろん単発UPDATEやINSERT SELECT置換が望ましい。loop updateはパフォーマンスの観点からは絶対に避けるべきなのだが、現実的には業務上の要求などからそうせざるを得ないケースもある。
この場合はcase updateが一つの選択肢となりうる。俺は達人に学ぶ SQL徹底指南書p.17「条件を分岐させたUPDATE」で知ったやり方で、今回の例では以下のようなSQLになる。
UPDATE TESTS SET column1 = CASE WHEN rowid = 'rowid1' THEN 'rowid1用の値' WHEN rowid = 'rowid2' THEN 'rowid2用の値' ELSE NULL END WHERE rowid IN ('rowid1', 'rowid2');
本来はROWIDでなくユニークインデックスの列をWHERE条件にするのだが、ともかく更新対象行を絞る条件をINに入れる。そして、そのINの分だけCASEで分岐し、各条件ごとの値で更新が行われるようにする。こうすると、上記の例でいえば、rowid1とrowid2で2回のUPDATEが必要だったのが、1回のUPDATEで済む。
これの効果はどの程度か。まず、execute countがloop updateでは約100万なのかcase updateでは訳50万と半減している。今回の例は2件で1UPDATEなので、CASEで分岐する件数を増やせば更に減らせる。
実行時間の影響はどの定義か。loop update約90秒引く単発update約40秒が約50秒、この数値がループ処理が余計に必要な処理時間といえる。case式によりこれが約半分となって約25秒とすると、単発update約40秒プラス約25秒の約65秒。実測値が約70秒なので、おおむね理論通りといえる。
しかし、逆に言えば、ただでさえ遅いループ処理のオーバーヘッド部分のみ半分になるだけ、とも言える。個人的にはこのテの小細工はあまり好きではない。まず、プログラムが複雑になる。上記の例だけでもcase updateだけ異様にコードが膨らんでいるのが分かる。どうしても最適化が必要であればやらざるを得ないが、まずシンプルなやり方を選んで、それで計測して遅いことが分かってから初めて最適化にとりかかるべきだろう。
おまけ
参照元の記事は「UPDATE 高速化」とかでぐぐってて見つけたものなんだけど。しかし、コレやったら絶対スピードアップする特効薬みたいなのがあるワケでもなく。
今回のエントリの趣旨からは外れるがUPDATEが遅くなる原因は色々考えられる。たとえば、WHERE条件がおかしかったり、適切なインデックスが無かったり、サブクエリがおかしかったりして、UPDATEが毎回テーブルの全表スキャンを行うなど。この辺はいくら表層的な最適化を頑張っても意味が無い。
そういう場合に必要なのは、まず計測し、本当に遅いかどうか、遅いとしたらその原因は何か、を調べる必要がある。Oracleの場合、実行計画、AUTOTRACE、SQLトレース、statspackとかそのあたい。Enterprise Managerが使えるならそれもアリ、SQL Developerでもソレナリなことは出来る。対策を打つのは、原因が数値データでハッキリしてからにしたほうが無難と思われる。
