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でループしながら投げる、という方式に変更することで、
処理が固まることはなくなりました。