【ORACLE】バッチ処理が一時表領域不足で異常終了したときのレアな原因と対処法
過去に遭遇したトラブルについてメモしておきます。運用保守の担当をしていた頃です。深夜３時頃、携帯電話が鳴りました。夜間バッチ処理が異常終了したお知らせでした。タクシーでプロジェクトルームへ。
どうやらパラレル実行したINSERT-SELECT文で一時表領域不足で異常終了していました。再実行してもダメ。結果その時はパラレル実行をやめると実行できました。
当時のＳＱＬ
下のような感じで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
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で複数問合せされることでメモリでは収まらなくなり、さらに一時表領域を使用。一時表領域が不足するほど肥大化していったのだと分かりました。
対策
今回は一時表領域の拡張はせず、ＳＱＬの改修を行いました。改修といってもヒントを一行追加しただけです。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
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
