過去に遭遇したトラブルについてメモしておきます。運用保守の担当をしていた頃です。深夜３時頃、携帯電話が鳴りました。夜間バッチ処理が異常終了したお知らせでした。タクシーでプロジェクトルームへ。
どうやらパラレル実行した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
