プランキャッシュの欠点とプランバインディングの解決策

プロブレムシナリオ：プランキャッシュとデータの偏り

背景

プランキャッシュは既存の実行計画を再利用することで生成時間を節約しますが、データの偏りがある場合 性能問題を引き起こす可能性があります。

例

テーブル t1 の定義とデータ分布は以下のとおりです：

CREATE TABLE t1 (
  c1 INT,
  c2 INT,
  c3 INT,
  KEY idx1(c1),
  KEY idx2(c2)
);

• データ分布は以下のとおりです：

  • c1 列の値 0 が1％、1 が99％を占めます。
  • c2 列の値 1 ～ 10 はそれぞれ10％を占めます。

プロブレム現象

SQL SELECT * FROM t1 WHERE c1 = ? AND c2 = ? の場合：

1. 初回実行（条件：c1=0 AND c2=1）：
   • オプティマイザーはインデックス idx1 を選択し、インデックスから c1 = 0 を満たす1％のデータを照会した後、インデックスを通じてテーブルにアクセスし、c2 = 1 で条件を満たさないデータをフィルタリングしてからテーブルに戻します。効率は高いです。
2. その後の実行（条件：c1=1 AND c2=1）：
   • 初回に生成されたプランを再利用し、idx1 を通じて c1 = 1 を満たす99％のデータを照会した後、インデックスを通じてテーブルにアクセスし、c2 = 1 で条件を満たさないデータをフィルタリングしてから返します。この場合、99％のデータをスキャンし、99％のデータをインデックスを通じてテーブルに戻してクエリを完了する必要があり、パフォーマンスは低いです。
   • より良い選択肢：idx2 インデックスを強制的に使用し、常に10％のデータをスキャンします。
   • 推奨されるソリューション：アウトラインを使用してSQLに対する実行計画をバインドし、このクエリリクエストが idx2 インデックスを使用するように強制できます。もちろん、このSQLに対しては、実際にはより良い最適化方法がありますが、本記事では議論しません。

解決策：プランバインディング（アウトライン）

OceanBaseは プランバインディング 機能を提供しており、ユーザーはSQLを変更することなく、OUTLINE を使用して実行計画を強制的に指定できます。

アウトラインの作成方法

1. SQL_TEXTを使用して作成：

   CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ]
   

   • 例：

     CREATE OUTLINE otl_idx2 ON SELECT/*+ index(T1 idx2)*/ * FROM t1 WHERE c1 = 1 AND c2 = 1;
     

   • 制限事項：

     • SQL形式（スペース、大文字と小文字）に敏感であり、構文の違いによってバインディングが失敗する可能性があります。

2. SQL_IDを使用して作成：

   CREATE [OR REPLACE] OUTLINE outline_name ON sql_id USING HINT hint;
   

   • 例：

     CREATE OUTLINE otl_idx2 ON '27F4FC32407331073407EAA24F5E5FA4'
     USING HINT /*+ index(T1 idx2)*/ ;
     

   • 利点：

     • SQL_ID に直接バインドされるため、構文の違いによるバインディング失敗を回避できます。
     • この方法を優先的に使用することを推奨します。

主要ポイントの説明

1. プロブレムの根本的な原因：プランキャッシュの再利用により、非最適なプラン がデータの偏りがある場合に繰り返し実行されます。

2. アウトラインの役割：実行計画を強制的に指定する（例えば、idx2 インデックスを選択する）ことで、データ分布の違いによるパフォーマンスの変動を回避します。

3. 推奨される実践：SQL_ID を使用してアウトラインを作成し、バインディングの信頼性を確保します。