プランキャッシュの欠点
前のセクションでは、パラメータ化されたSQLに対して実行計画を生成した後、その計画をプランキャッシュに保存し、以降のSQL実行でその計画が再利用される仕組みについて説明しました。このような計画の再利用は計画生成時間を節約できますが、データの偏りがあるシナリオでは悪いケースを隠れて含んでいます。ここで例を挙げて説明します。
テーブルt1の定義は以下のとおりです。c1列には2種類の値しかなく、値0が全データの1%、値1が99%を占めていると仮定します。c2列には1から10までの10種類の値があり、それぞれ10%ずつを占めています。すると、SELECT * FROM t1 WHERE c1 = ? AND c2 = ?というSQLについて、計画生成時にオプティマイザーが見た条件がc1 = 0 and c2 = 1だった場合、オプティマイザーはインデックスidx1を使用することを選択します。まずインデックスからc1 = 0を満たす1%のデータを取得し、その後インデックスを使ってテーブルに戻り、C2 = 1で条件を満たさないデータをフィルタリングして返します。この場合、1%のデータをスキャンし、インデックスを使ってテーブルに戻って1%のデータを取得するだけでクエリが完了します。しかし、もしその後サーバーが別のSQLを受信し、ユーザーが指定した条件がc1 = 1 and c2 = 1に変わった場合、以前に生成された計画を再利用すると、まずインデックスidx1からC1 = 1を満たす99%のデータを取得し、その後インデックスを使ってテーブルに戻り、C2 = 1で条件を満たさないデータをフィルタリングして返すことになります。この場合、99%のデータをスキャンし、インデックスを使ってテーブルに戻って99%のデータを取得する必要があります。明らかに、このSQLに対してはインデックスidx2を使用する方が良い選択です。なぜなら、どのような値を取る場合でも、idx2を使用すると常に10%のデータをスキャンし、テーブルに戻って10%のデータを取得するだけで済むため、SQLの実行時間がより安定するからです。
create table t1 (
c1 int,
c2 int,
c3 int,
key idx1 (c1),
key idx2 (c2)
);
このような状況に遭遇した場合、ユーザーはoutlineを使用してSQLに実行計画をバインドし、このクエリリクエストがidx2インデックスを使用するよう強制できます。もちろん、このSQLに対しては実際にはもっと良い最適化方法がありますが、これは本記事の議論の焦点ではないため、詳細には触れません。
プランバインディング
プランバインディングは、OceanBaseデータベースが提供する仕組みで、SQLを変更することなく、データベース内にoutlineを作成することで実行計画を制御できるようにします。現在、OceanBaseデータベースでは2種類のoutline作成方法を提供しており、構文は以下のとおりです。
/* SQL_TEXTを使用してOutlineを作成 */
CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ]
/* SQL_IDを使用してOutlineを作成 */
CREATE [OR REPLACE] OUTLINE outline_name ON sql_id USING HINT hint;
上記のSQLについて、以下の方法でoutlineを作成し、OceanBaseデータベースに対してこのSQLの実行時にインデックスidx2を使用するよう要求できます。注意点として、OceanBaseデータベースは2つのパラメータ化されたSQLが同じかどうかを判断する際、スペースや大文字小文字を区別しないため、SQL_TEXT方式でoutlineを作成すると、create outlineステートメントに書かれたSQLと実際に実行されるSQLが完全に一致しないことがあり、その結果outlineがバインドできない問題が発生しやすいです。そのため、SQL_ID方式でoutlineを作成することを推奨します。
/* SQL_TEXT方式 */
CREATE OUTLINE otl_idx2 ON SELECT/*+ index(T1 idx2)*/ * FROM t1 WHERE c1 = 1 AND c2 = 1;
/* SQL_ID方式 */
CREATE OUTLINE otl_idx2 ON '27F4FC32407331073407EAA24F5E5FA4'
USING HINT /*+ index(T1 idx2)*/ ;