理系学生日記

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

忍者TOOLS

SQL にはバインド変数をちゃんと使いましょうという話

先に断っておくと、Oracle ベースの話。
SQL インジェクション云々の問題を除いても、実行する SQL にはバインド変数を使うのが正しい、という命題は OLTP に限っていえばほぼ真と言って良いと思う。ここで "限って"という限定を用いているのは、一部にはバインド変数を使わない方が良いというケースもあるため。

バインド変数を使用しない場合であれば、毎回 SQL の hard parse が行われ、オプティマイザによって、バインド変数に埋め込まれた値を考慮した実行計画が立てられる。例えば、バインド変数に埋め込まれた値がデータ上頻出しないようなものであれば、(使用可能であれば)索引を使用した形のアクセスになるだろうし、逆であればフルスキャンが選択されたりする。
一方、バインド変数を使用していると、初回の hard parse によって立てた実行計画が、後段の SQL でも再利用される(9i 以降で導入された bind peek 機能。デフォルトで有効)。この機能の落とし穴については、以下のエントリで分かりやすくまとめられている。


だったらリテラル使ったほうが良いじゃん、という話になってしまうが、それでもバインド変数を使いましょうというのは、リテラルを使った場合にこの落とし穴以上のデメリットがあるため。特に B2C のシステムのような多数のアクセスと、それに伴う同時並行した DB アクセスが相当数発生し得るシステムでは文字通り致命的になり得る。


バインド変数を使っていると、hard parse で生成される大半の解析情報というのは、SQL 間で共有できる。再利用できる。このため、不要な hard parse の頻度は相当少なくなる。
hard parse 処理は latch を大量に要求する。latch とは何かというと、DBMS が内部で要求するロック機構で、その目的は同期制御。DBMS は、latch によって同時並行アクセスによる特定のメモリ空間の破壊や、内部データの齟齬を防止している。
本質は同期制御なので、当然ながら latch を要求する部分では処理が直列に並ぶ。同時並行した DB アクセスが、その箇所では一列に並んで前段の要求が処理されるのを待つことになる。いわゆる latch 待ち、latch 競合の発生である。latch 競合は、軽微であれば問題にはならないが、これが増加すると明確にレスポンスタイムに効いてきて、最悪の場合はまるで DB がハングしたのと同じような状態に陥る。
これは、要求数が多くなるほど発生しやすくなるはずだけれど、要求数の増加は基本的にはユーザ数の増加なので、ここを絞るわけにもいかない。いかにして latch 待ちを防ぐかは、まずいかにして hard parse を防ぐか、解析情報を共有するかという問題に帰結する。これを達成する基本は、発行される SQL にはリテラルでなく、バインド変数を使いましょうということになる。