理系学生日記

おまえはいつまで学生気分なのか

Oracle の実行計画が振れてしまう

数百万レコードを含むテーブルをいくつか JOIN する SELECT 文が数十分かかっても返ってこないという事象が時々発生する。bind 変数の値以外は同じ SQL を流しても、遅いときと早いときがあり、ちょっとした問題になる。
実行計画を見る限りでは、遅いときと早いときでどのインデックスをどういう順番で使っているかという部分に差があり、SQL 実行時の統計情報が異なるために異なる SQL が使われれているのだろうと推測される。

当該 SQL に対する実行計画として

  • 遅い実行計画 (Plan 1)
  • 早い実行計画 (Plan 2):ただしバインド変数の値によっては極度に遅くなる

があり、Plan 2 の方が SQL のコスト的には低い。結果として Oracle は Plan 2 を用いようとするが、バインド変数の値によっては Plan 1 の方が圧倒的に早い(逆に言えば Plan 2 が極度に遅い) 場合がある。そういう値をバインドした SQL を実行すると、統計情報が変化して Plan 1 の方が使われ出すことになる。しかし、実質的には Plan 2 の方がコスト的には低い実行計画であるため、時間が経過すると Plan 2 が使われるようになり、特定のバインド変数によって、また数十分 SQL の結果が返却されない事象が発生する。

物事をシンプルに捉えれば、その特定のバインド変数の場合用に SQL を別個作成し、Plan 1 となるようなヒント句を与えるなどすれば問題は解決するようにも思える。ただし、当該 SQL はプログラムから動的に生成されるものであり、ユーザから指定されるパラメータによって WHERE 句の追加やサブクエリの追加が頻繁に発生する。それぞれの WHERE 句、サブクエリを考慮せずに下手にヒント句を指定してしまうと、速度の劣化する場合なんてのも起こり得るし、それぞれの追加パターンについて速度を劣化させないようなヒント句の当て方が果たしてあるのかというと甚だ疑問である。
また、せっかくまとめられている SQL を別個作成する(いくつもの SQL に切り出す)というのは、そもそも生産的な活動だとは思えない。しかし、簡便かつ他の SQL に影響を与えない方法というのがなかなか思いつかない。