Oracleバッチ処理が一時表領域不足で異常終了したときのレアな原因と対処法

 過去に遭遇したトラブルについてメモしておきます。運用保守の担当をしていた頃です。深夜3時頃、携帯電話が鳴りました。夜間バッチ処理が異常終了したお知らせでした。タクシーでプロジェクトルームへ。

 どうやらパラレル実行したINSERT-SELECT文で一時表領域不足で異常終了していました。再実行してもダメ。結果その時はパラレル実行をやめると実行できました。

当時のSQL

 下のような感じでORが複数ありました。
【例】

insert into TABLE1
 select
  *
 from
 TABLE2
 where
  USER_ID = A0001
  OR USER_ID = B0001
  OR USER_ID = C0001
  OR USER_ID = D0001
  OR USER_ID = E0001


原因はOR拡張されたこと

 実行計画はOR拡張され、UNIONALLを使用して複数問合せに変換されていました。UNIONALLで複数問合せされることでメモリでは収まらなくなり、さらに一時表領域を使用。一時表領域が不足するほど肥大化していったのだと分かりました。

対策

 今回は一時表領域の拡張はせず、SQLの改修を行いました。改修といってもヒントを一行追加しただけです。NO_EXPANDというヒントで、OR拡張をしないようにオプティマイザに指示するものです。見事に解決しました。
 【修正例】

insert into TABLE1
 select /*+NO_EXPAND*/
  *
 from
  TABLE2
 where
  USER_ID = A0001
  OR USER_ID = B0001
  OR USER_ID = C0001
  OR USER_ID = D0001
  OR USER_ID = E0001