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

オラクルマスター教科書Oracle Database Gold 12(トゥエルブ(新機能編) iStudyオフィシャルガイド [ 代田佳子 ]

価格:6,696円
(2018/11/19 19:20時点)