このエントリでは、Oracle DB についての挙動理解の整理をしたいとおもいます。
ヒストグラム統計
SQL の実行計画を作成するのはオプティマイザであるわけですが、Oracle のオプティマイザが実行計画を立てるとき、基本的には列値は一様分布に従うという仮定を置きます。このため、このような仮定に沿わないデータ分布を持つテーブルがある場合、オプティマイザは時にとんでもない実行計画を作成する場合があります。
当然ながらこの問題を Oracle も認識していて、それを改善するために収集するのがヒストグラム統計です。これは(Oracle 内部の)統計情報更新ジョブによって収集されるもので、どのくらいの値がどの程度あるのかといった列値の分布情報を収集し、それを実行計画作成時に参照するような動作をします。
たとえば、1億レコードを持つテーブルで、1〜10 までの値が 9999 万レコード存在し、残りの 1 万レコードだけが 1000 を取るような偏った列値を持つようなデータ分布があるとします。このときに、
select * from example_table where column > 10
といった SQL を発行すると、データ分布を知っている人間からすれば「選択率 0.1 % なんだからインデックスアクセスの方が良いだろ常考」となるわけですが、一様分布を仮定してしまうと、「選択率 99.9 % だからフルスキャンの方が圧倒的高速だろ常考」となり、悲劇的な状況が発現し得ます。
このような「誤ったデータ分布の仮定」を修正するために、ヒストグラム統計が収集されるわけですね。
ヒストグラム統計には、列値の種類に従って「Frequency」と「Height Balanced」ヒストグラムの 2 種類があるわけですが、詳細はこのあたりでも読むといいとおもう。
CURSOR_SHARING
全く話は変わりますが、OLTP システムにも関わらず SQL にバインド変数を使用しない、みたいなアプリケーションというものが世の中には数多存在しており、ときにサービス提供に対して致命的な影響を与え得ます。
このような場合、「リテラルが指定される SQL をバインド変数化せよ」という話になるのですが、大規模な改修が発生するため、なかなかこの実行に踏み切れないことが多いです。この問題を解決するため、Oracle には CURSOR_SHARING という設定パラメータが存在しています。このパラメータを SIMILAR、または FORCE に設定することで、リテラルが指定された SQL を「Oracle が内部的にバインド変数に置き換え」てくれます (デフォルト値は EXACT であり、この場合はバインド変数への置換は実施されない)。
この SIMILAR、FORCE の違いについて理解するためには、さらに bind peek という機能を理解しておくと分かりやすいです。
bind peek 機能
バインド変数を使うと何が良いのか、というところから話を始めますが、バインド変数を使用する主たる目的は、実行計画 (正確にはカーソル？)の共有です。
実行計画の作成というのは、DBMS としては負荷の高い処理です。SQL のパースから始まって、テーブルの存在チェックや権限の確認といったところで内部表への多数のアクセスが発生します。また、共有メモリにもデータを格納したりするので、並列性の敵であるロックも必要になります。このため、実行計画を共有できるのであれば共有した方が良い。
それを実施できるようにするのがバインド変数化で、要するに、既存の実行計画を使うことを前提に、埋め込む値だけ変えてしまおうぜ、というものです。
ここで問題になるのは、「既存の実行計画」とやらが、「今回埋め込む値」に対して最適な実行計画なのか、というものです。Oracle からすれば、どんな値がバインドされるか分からないうちに作った実行計画となるので、自明ですがこの命題は否定されます。最適にできるわけがない。実行計画の最適さを求めるんだったら毎回実行計画を作成させろ。リテラルを使え。
では、このときの実行計画は何を前提に作られるの、という疑問が湧きますが、これは Oracle の未公開パラメータである _optim_peek_user_binds 変数によって制御されます。
この変数値が TRUE の場合、「バインドピーク」機能が有効になります。バインドピーク機能が有効な場合、Oracle は初回の実行計画生成時、バインド変数にバインドされる変数値を元に実行計画を生成し、その後は当該の実行計画を共有します。つまり、「初回にバインドされる値が、実行計画を決定する」ということです。
(なお、バインドピーク機能が無効な場合は、Oracle が選択率を決め打ちして実行計画を生成します)
CURSOR_SHARING=SIMILAR
bind peek の説明が終わったところで、CURSOR_SHARING に話を戻します。リテラルをバインド変数化するための設定値として SIMILAR と FORCE があるという話を出しましたが、この SIMILAR というヤツはなかなかの曲者で、実際に Oracle 10g には存在していますが 11g からは削除(実際には、もうちょっと賢い設定値が追加)されています。
SIMILAR と FORCE の違い、それを一言で表すと「実行計画に最適性を求めるか否か」です。
FORCE の説明は簡単で、CURSOR_SHARING=FORCE とすると、リテラルの値がどうであっても、SQL の構文さえ同じであれば実行計画を共有します。実行計画が、バインドされる値に対して最適であろうがなかろうが有無を言わさず共有です。カッコいい。
一方で、CURSOR_SHARING=SIMILAR は「最適な実行計画にならない可能性があるのであれば、共有しない (子カーソルを作成する)」という動作をします。
では、この「最適な実行計画にならない可能性」をどう判断するかというと、例えば、冒頭に説明したヒストグラム統計の有無です。ヒストグラム統計が収集されている場合、Oracle はその情報を参照して「より良い実行計画を作成できる可能性」がありますから、既存の実行計画を共有しません。言い換えれば、実行計画を作成しようとします。
結果として、CURSOR_SHARING=SIMILAR は、バインド変数化のメリットを消す方向に働く、そんな理解をしています。
