OracleDatabase
2019-10-14

疑問

大量のデータ挿入を(再処理時にはUPDATE、初回処理時はINSERTを区別したくないため)MERGEを使って行いたい時、
DirectLoadInsertを使うことで早くなりそうだということは分かったが、
いざ使ってみるとenq: HW - contentionにぶち当たり全然性能が出なかった。
(enq: HW - contention:表領域が使用している最大セグメント位置HighWaterMarkを更新するためにロックするが、それ待ち)
参考にしたサイト: https://www.ex-em.co.jp/oracle-k/oracle-event-%E8%A7%A3%E8%AA%AC/2912/

1000件程度のレコードの挿入を20〜30並列で実行するだけでロック待ちが頻発し、10分程度要する状態。

#sh(sql){{

/*
tab_1テーブルにレコードを追加したい
tab_1は主キー pri_key_columnを持っている
基本的に存在するレコードは新しいデータで上書きさせたい
*/

MERGE /*+ APPEND */ INTO tab_1 T1
USING (SELECT pri_key_column from FROM DUAL) T2
ON (T1.pri_key_column = T2.pri_key_column)
WHEN MATCHED THEN
UPDATE SET col1 = val1, col2 = val2
WHEN NOT MATCHED THEN
INSERT (col1, col2)
VALUES (val1, val2)
;

}}

ALTER TABLE ${TABLE} DISABLE TABLE LOCK;すると当然ながらロックできなくなるので、このロック待ちに費やされる時間は激減しパフォーマンスは劇的に向上したのだが、果たしてこれで良かったのだろうか
テーブルロックが無効化されていて問題はないのだろうか、疑問が残る。

DirectLoadInsertを使おうと思った理由

参考:https://www.shift-the-oracle.com/inside/direct-pass-insert.html
一時セグメントに挿入するデータを書き、HWが指す位置からまるっとコピーする方法。
大量のINSERTをするときにはDirectLoadInsertを使うと早くなるよ、と調べたら出てきたので使ってみただけ。

HWの位置を覚え、そこからまるっとコピーしHWを更新するため、テーブルのロックが必要。

DirectLoadInsertをしても、結局早くなかった

冒頭の通り、enq: HW contentionによるプロセスの待ちが生じ、結局INSERTが早くなったのかなんなかったのかよくわからない。
(通常のINSERTに比べたらREDOログの生成量が減ったりして若干早いのかもしれないが、待ちの量が大きすぎて「遅い」ままであることに変わりなかった)
  とりあえずこいつのせいで遅くなっていることだけは十分わかった。

ALTER TABLE *** DISABLE TABLE LOCK

https://www.oracle.com/technetwork/jp/database/in-memory/overview/twp-bp-for-iot-with-12c-042017-3679918-ja.pdf
ちょうど8ページ目くらいにこう記載がある:

ロック・オーバーヘッドを軽減するには、表レベル・ロックを無効にすることを検討するとよいでしょう。表レベル・ロックを無効にすると、オブジェクトでDDLコマンド(DROP、TRUNCATE、ADD COLUMN、その他)が実行されなくなります。また、トランザクションの開始前に、共有された表レベル・ロックを保護する必要がなくなるため、各INSERT文の処理を迅速化することもできます。
 ALTER TABLE Meter_Readings DISABLE TABLE LOCK;
 図6:表レベル・ロックの無効化
表でDDLコマンドを実行するには、表ロックを再び有効にする必要がある点に注意してください

Oracle公式の文書に言わせると、ロック待ち(というかそもそものオーバーヘッド)を軽減するには、不要ならロックを無効化したらどうかという。
テーブル単位でのロックが必要かどうかは後で確認するとして、DISABLE TABLE LOCKしてみたところ、速度が劇的に改善。
ここでロックを無効化してもいいのか疑問が残った。

ALTER TABLE ${TABLE_NAME} DISABLE TABLE LOCKの意味は

文字通り、指定したテーブルのロックを無効化する。
例えばDROPやTRUNCATEしようとした場合にはテーブルロックできないためORA-00069エラーで失敗するようになる。
http://www.dba-oracle.com/t_disable_table_lock.htm

テーブルロック

参考:https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsm18-1610822-ja.html
DMLを発行しても標準では必ずテーブルロックが発生する。これは、データ一貫性を保つためDML実行中にDDLの実行を防ぐ目的。

ALTER TABLE ${TABLE_NAME} DISABLE TABLE LOCKを発行すると、テーブルロックが無効、つまりDDLが一切出来なくなるのは上記の通りだが
DDLに「今更新するなよ」と通知する目的でロックを取得するDMLについては、ロックを取得せずそのまま実行されるようになるのだろう。

参考:https://oraclespin.com/2012/05/13/example-shows-how-disable-table-lock-works/
DMLはOK、DDLもカラムの追加はできるが変更や削除はロックできないため失敗する。

以下、説明から想像するに
【DISABLEにする前】

  • 接続A
    • INSERT処理を開始、同時にテーブルロックを取得
    • 長いINSERTを開始... (→行ロック取得)
      • データ増加に伴いHWMを随時更新...
  • 接続B
    • INSERT処理を開始、別スレッドがテーブルロックを取得中だが、DMLなので継続
    • 長いINSERTを開始... (→行ロックは取得可能)
      • データ増加に伴いHWMを随時更新しようとするが、テーブルロック取得中なので更新できず待ち発生...

【DISABLEにした後】

  • 接続A
    • INSERT処理を開始
    • 長いINSERTを開始... (→行ロック取得)
      • データ増加に伴いHWMを随時更新...
  • 接続B
    • INSERT処理を開始
    • 長いINSERTを開始... (→行ロックは取得可能)
      • データ増加に伴いHWMを随時更新...

HWロックはできなくなるのか

HWMの更新はキューに登録して更新を待つ方式なので、ここでの競合はないだろう。
(HWロックとは、実際にロックされるわけではなく、HWの更新にともなうキューの待ちで、実際にはテーブルのロックと思われる)

よって、テーブルロックの無効化を行っても、パフォーマンスの改善だけでデメリットはなさそうだ。

補足:そもそものAPPENDヒントを考えてみる

MERGE /*+ APPEND */ INTOについて、こちらに解説があった。
http://www.dba-oracle.com/t_append_upsert_merge_sql.htm

  • HWについてはpctfreeがいっぱいになった時のみ引き上げられる
  • APPENDヒントは、新しいブロックが必要になった場合に、常に新しいブロックを取りに行くように指示するだけ(つまり、すでに確保済みのブロックを使う場合にはなにも作用しない)

補足:一般的にINSERTを早くするには

ETLの一環でINSERTする場合には上述の通りとなるが
今回調べてみて、あるテーブルから別のテーブルにINSERTする時のパフォーマンス比較について述べているページを見つけたのでメモしておく。
http://www.dba-oracle.com/t_insert_tuning.htm
SQL Loaderが最速らしく、逆に通常のMERGE INTOはSQL Loaderの約18倍かかるそうで。


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2019-10-15 (火) 08:51:39