DBからSQLの結果が返ってこない

[umemura]

★point
　Oracle では、SQLのサイズを64kバイト未満に抑えよう

ある案件で、ある損益帳票を、社内の全部門で集計する、という処理が、
固まってしまう、という問題が起きました。

現象としては、Javaから、iBatis経由でSQLをOracleに発行した後、
DBサーバのCPU利用率が100%にまで上がってしまい、
結果が返ってこなくなる、というものでした。


この時に発行されているSQLは、IN句のなかに数百の部門が展開されるているもので、
数千行という、かなりの大きさになっていました。

また、返却されるデータ量も、数万件という、そこそこの件数なので、
そのあたりがDBサーバの負荷になっているのではないか、という予想のもと、
いろいろと調査をしたのですが、
下記のような矛盾があり、原因の特定ができず、困っていました。


・該当の処理も、必ず固まるわけではない（条件は特定できない）
・返却されるデータの大小にかかわらず、固まることがある
・同じSQLを、SQL Developer から投げると大きな遅延なく結果が返却される


いろいろと頭をお悩ませたのですが、最終的には、下記のサイトを見て、
どうやら、SQLの大きさに原因があるのではないか、ということに落ち着きました。
たしかに、固まるSQLとそれ以外のSQLの間には64kバイトの壁がありました。


■Oracle 性能限界
http://www.shift-the-oracle.com/oracle/limits.html
「SQLにおける制限事項」


（とはいえ、利用しているOracleのバージョンは11gなので、
　この64kバイト制限が9iまでの制限という情報とは矛盾があるのですが・・・）

結局、全部門をまとめてIN句に入れるのではなく、部門ごとのSQLを、
Javaでループしながら投げる、という方式に変更することで、
処理が固まることはなくなりました。

[umemura]

SQLを修正してサイズを小さくしたところ、問題が解消したように思えたのですが、
同じ処理を何度か動かすと、以前と同様にDBから帰ってこない現象が発生しました。

発生頻度は下がっているようなので、SQLのサイズ低減も無意味ではなかったようですが、
根本的な解決ではないようです。

SQL Developer で実行計画を見てみると、コストが30万を超しているのですが、
これってさすがにでかすぎるのかしらん。
あまりコスト値はあてにならない、という話も聞くのですが・・・。

いずれにせよ、継続調査中です。
なにかヒントになる情報をお持ちの方がいらっしゃったら教えてください。

[umemura]

クエリの結果が返ってこない問題ですが、解決しました。

最終的な結論としては、「オプティマイザ・フィードバック」という機能に関するバグらしく、
この機能は、SQLの実行計画作成時に、統計情報を使って、同じSQL実行結果を考慮してくれる、
というものらしいのですが、、
この処理の際に、「アウトラインやSQLプロファイルを使用している場合に
TABLE 句とバインド変数を含むクエリ」だと、実行計画を正しく生成できない
という非公開バグがあるようです。


問題が起こるクエリ例：
SELECT ... FROM (SELECT ...FROM table(table_function (plsql) expression))


■オプティマイザ・フィードバックとは
Oracle の ver 11.2から追加され、
これは、SQLの実行計画作成（ハードパース）時に、夜間等に収集されてるOracleの統計情報から、
実データのカーディナリティ（データの種類の多さ）を合わせて考慮することで、
実行計画の制度を上げることを目的とした機能・・・らしい。
これによって、同じSQLでも、実行計画が都度変わる可能性がある・・・らしい。


解決策は、このオプティマイザ・フィードバック機能を、
Oracleパラメータの設定で無効にする（_optimizer_use_feedback = false）ことで、
同対応をしたところ、今のところこちらの環境では、同現象が再現されなくなっています。

ちなみに、こちらの環境（11.2.0.1）でのみ発生する稀な問題で、
最新のマイナーバージョン（11.2.0.2）であれば、
同バグは修正されているようです。


今回の教訓は
「とりあえず最新の環境でためしてみろ」
「わからないことは素直にサポートに聞け」
という2点ですね。

[adilmalik]

※Curlのメモリ使用量は、「タスクマネージャ」の「プロセス」タブで表示される
　「surge,.exe」の「メモリ（プライベートワーキングセット）」を参照しました