oracleで大量データ削除時にLOCKと思われる事象が生じる問題について

Question

お世話になります。初めて質問させて頂きます。
あるテーブルに大量のデータがあるのですが、
「営業日」という列が「削除基準日」以下に該当すれば
削除しようとしています。

営業日の条件だけでは数百万件が該当するため、
ROWNUMを用いて1回のDELETE文で削除するのは最大3万件とし、
3万件削除の都度COMMITしています。

DELETE FROM 削除対象テーブル
WHERE 営業日<=削除基準日 AND ROWNUM<=30000;
COMMIT;

上記SQLで削除された件数が3万件未満になるまで、ループして発行するイメージです。
1回の処理（DELETE発行からCOMMIT完了までの処理）は、調子が良ければ数秒で終了します。

ところが上記SQLを何度も繰り返すと、途中で結果が戻ってこなくなります。

(1)削除対象テーブルは、別のセッションからはアクセスしておらず、別セッションでLOCK等は行っていません。
(2)結果が戻って来なくなったセッションを強制終了（alter system kill session 'sid,serial#';）後、10分程度何もせず、再開すると、削除は可能です。
(3)但し、(2)で再開しても再び何度目かのループで、結果が戻ってこなくなります。

上記のような問題には、どのように対応すれば良いでしょうか？
ご存知の方がおられたらアドバイスを頂ければと存じます。

なお環境はLinux版Oracle11.2.0.4.0です。

以上よろしくお願いします。

Answer 1

Enterprise Editionであればダイレクトパスインサートを利用することで処理を高速化できると思います。
Oracleのダイレクトパスインサートを使ってDelete処理を高速化する

REDOログの書き込み処理がネックになっている可能性もあるのでCOMMIT WRITE BATCH NOWAITでコミットを行ってみるのも効果があるかもしれません。
COMMIT

Comments

[MAD_Engineer]

お世話になります。ご回答ありがとうございます。
結論から記すと、「3万件削除」で削除対象がゼロになるまでループするのではなく、
「最大N回または削除対象がゼロになる」までのループとし、
その後、しばらくのインターバルをおいて繰り返す方式にしました。

ダイレクトパスインサート等を現在のシステムで使用するには、技術・運用面での知識等が不足しており、今回は見送らせて頂きましたが、将来に備え、勉強したいと思います。

ありがとうございました。

Answer 2

Oracle詳しくないので大した解決策はありませんが、一般的にありそうな原因と合わせて少し。

原因推察：
非同期レプリケーションを行っているという事はないでしょうか。
この場合、マスタでのcommit完了が返ってからレプリケーション作業が始まりますので、
連続で重い処理を投げていると、マスタ内でもキューが溜まり、性能が劣化する可能性があるかと思います。
ご提示の状況を見るに、これが一番ありそうな気がします。

解決策案：
今更出来ない事な気はしますが、削除基準日というのが月に一度とかであれば、
それを基準としてパーティションに分けておくことです。
パーティションに分けている場合、削除はパーティション自体を消せばいいので、遥かに高速に動作するはずです。

あまり現実的な解決策でなくて申し訳ございませんが、
ご参考になれば幸いです。

Comments

[MAD_Engineer]

お世話になります。ご回答ありがとうございます。
sho_cs様へのコメントと重複する点もありますが、
「3万件削除」で削除対象がゼロになるまでループするのではなく、
「最大N回または削除対象がゼロになる」までのループとし、
その後、しばらくのインターバルをおいて繰り返す方式にしました。

パーティション分割は、もし現在のシステムを更改する機会があれば、ぜひ採用したい技術です。（非同期レプリケーションについて、詳しくは知らないのですが、分散DB環境のことでしょうか？当方のOracleサーバーは1台でレプリケーションは行っておりません。）

ありがとうございました。

Answer 3

関係ない方面から…。
正常時と、異常時の SQL 発行、あるいは、動作が結果が戻って来ない間、サーバの負荷、処理状況(top や iostat の様子)はどうでしょうか。それを見ると、何かわかることがある…かもしれません。

Comments

[MAD_Engineer]

takotakot様

お世話になります。コメントありがとうございます。
当時、他のセッションには悪影響がなかった（と思われる）のでiostat等による調査は行いませんでした。
なおこの件は、どうやらハイウォータマークが関連しているのではと推測しています。
現在は当時と別案件を担当しているのですが、機会があればiostat等での調査も検討してみます。

ありがとうございました。