クイックパラメータ化

パラメータ化とは、SQLクエリ内の定数を変数に置き換えるプロセスです。

ファストパラメータライズによる実行計画の取得

同じSQL文が毎回実行される際に異なるパラメータを使用する場合があります。これらのパラメータをパラメータライズ処理することで、具体的なパラメータに依存しないSQL文字列を得ることができ、この文字列をプランキャッシュのキーとして使用して実行計画を取得します。これにより、パラメータが異なるSQLでも同じ実行計画を共有できるようになります。

従来のデータベースでは、パラメータライズ時に一般的に構文木をパラメータライズし、その後パラメータライズされた構文木をキーとしてプランキャッシュから計画を取得します。一方、OceanBaseデータベースでは、テキスト列を直接パラメータライズしてプランキャッシュのキーとして使用するため、これをファストパラメータライズと呼びます。

ファストパラメータライズに基づいて実行計画を取得するプロセスは、以下の図のとおりです：

ファストパラメータライズに基づく実行計画キャッシュの利点は以下のとおりです：

• 構文解析プロセスが省略されます。

• Hash Mapの検索時に、パラメータライズされた構文木に対するハッシュ値の計算と比較操作を、テキスト列に対するハッシュ値の計算とMEMCMP操作に置き換えることで、実行効率を向上させることができます。

定数パラメータ化と制約条件

OceanBaseデータベースでは、特定のシナリオにおいて定数はパラメータ化できません。パラメータ化できない制約条件は以下のとおりです：

• すべての ORDER BY 句の後の定数（例：ORDER BY 1,2;）。

• すべての GROUP BY 句の後の定数（例：GROUP BY 1,2;）。

• フォーマット文字列として使用される文字列定数（例：SQL文 SELECT DATE_FORMAT('2006-06-00', '%d'); の %d）。

• 関数の入力パラメータであり、関数の結果および実行計画に影響を与える定数（例：SQL文 CAST(999.88 as NUMBER(2,1)) の NUMBER(2,1)、または SUBSTR('abcd', 1, 2) の 1 および 2）。

• 関数の入力パラメータであり、暗黙の情報を含み、実行計画に影響を与える定数（例：SQL文 SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012'); の "2015-11-13 10:20:19.012" は、入力タイムスタンプを指定すると同時に、関数処理の精度がミリ秒であることを暗黙的に指定しています）。

一般的な定数パラメータ化の例

既存のSQLクエリステートメントの例は以下のとおりです：

obclient> SELECT * FROM t1 WHERE c1 = 5 AND c2 ='oceanbase';

上記の例で、OceanBaseデータベース内部でパラメータ処理を行った後のSQLクエリの結果は次のとおりです。定数 5 と oceanbase はパラメータ化され、変数 @1 と @2 に変わります。そして、現在のデータベースで実行済みのSQLと照合し、対応する実行計画をマッチングします。

obclient> SELECT * FROM T1 WHERE c1 = @1 AND c2 = @2;

定数はパラメータ化できない例

しかし、プランマッチングでは、すべての定数がパラメータ化できるわけではありません。例えば、ORDER BY 句の後ろに続く定数は、SELECT で投影された列のうち、何番目の列に従ってソートするかを示しているため、パラメータ化することはできません。

サンプルテーブル t1 を作成し、適切なデータを挿入します。テーブル t1 には c1 列と c2 列が含まれており、c1 列は主キーです。

obclient> CREATE TABLE t1(c1 INT PRIMARY KEY,c2 INT);
Query OK, 0 rows affected

obclient> INSERT INTO t1 VALUES (1,2);
Query OK, 1 row affected

obclient> INSERT INTO t1 VALUES (2,1);
Query OK, 1 row affected

obclient> INSERT INTO t1 VALUES (3,1);
Query OK, 1 row affected

• 以下のSQLクエリを実行し、結果を c1 列でソートします。c1 列は主キーであるため順序付けられているため、主キーによるアクセスを使用することでソートを省略できます。

  obclient> SELECT c1, c2 FROM t1 ORDER BY 1;
  +----+------+
  | C1 | C2   |
  +----+------+
  |  1 |    2 |
  |  2 |    1 |
  |  3 |    1 |
  +----+------+
  3 rows in set
  
  obclient> EXPLAIN SELECT c1, c2 FROM t1 ORDER BY 1;
  
  Query Plan:
  | ===================================
  |ID|OPERATOR  |NAME|EST. ROWS|COST|
  -----------------------------------
  |0 |TABLE SCAN|t1  |1000     |1381|
  ===================================
  Outputs & filters:
  -------------------------------------
    0 - output([T1.C1], [T1.C2]), filter(nil),
        access([T1.C1], [T1.C2]), partitions(p0)
  

• 以下のSQLクエリを実行し、結果を c2 列でソートする場合、明示的なソート操作が必要となります。実行計画は以下の例のようになります：

  obclient> SELECT c1, c2 FROM t1 ORDER BY 2;
  +----+------+
  | C1 | C2   |
  +----+------+
  |  2 |    1 |
  |  3 |    1 |
  |  1 |    2 |
  +----+------+
  3 rows in set
  
  obclient> EXPLAIN SELECT c1, c2 FROM t1 ORDER BY 2;
  
  Query Plan:
  | ====================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST|
  ------------------------------------
  |0 |SORT       |    |1000     |1886|
  |1 | TABLE SCAN|t1  |1000     |1381|
  ====================================
  Outputs & filters:
  -------------------------------------
    0 - output([T1.C1], [T1.C2]), filter(nil), sort_keys([T1.C2, ASC])
    1 - output([T1.C1], [T1.C2]), filter(nil),
        access([T1.C1], [T1.C2]), partitions(p0)
  

したがって、ORDER BY 句の後ろの定数をパラメータ化すると、異なる ORDER BY の値が同じパラメータ化されたSQLを持つことになり、誤った計画がヒットする原因となります。

定数パラメータ化における誤マッチング問題の解決

定数パラメータ化において生じうる誤マッチング問題を解決するため、ハードパースによって実行計画が生成される過程で、SQLリクエストに対して構文木解析を用いたパラメータ化が行われ、それに伴う不整合な情報が取得されます。例えば、あるステートメントに対応する情報が「高速パラメータ化により、パラメータ配列の3番目の要素は数字3でなければならない」というものであれば、これを「制約条件」と呼ぶことができます。

Q1クエリ SELECT c1, c2, c3 FROM t1 WHERE c1 = 1 AND c2 LIKE 'senior%' ORDER BY 3; について、構文解析を行うと、パラメータ化後のSQL文は以下のようになります。パラメータ配列は {1，'senior%'，3} です。

obclient> SELECT c1, c2, c3 FROM t1 WHERE c1 = @1 AND c2 LIKE @2 ORDER BY @3;

ORDER BY 句の後ろの定数が異なる場合、同じ実行計画を共有することはできません。そのため、構文木解析によるパラメータ化では別のパラメータ化結果が得られます。次の例のように、Q1クエリに対応するパラメータ配列は {1，'senior'} であり、制約条件は「高速パラメータ化により、パラメータ配列の3番目の要素は数字3でなければならない」となります。OceanBaseデータベースは、Q1クエリに対して新しく生成されたパラメータ化後のテキスト、制約条件、および実行計画をすべてプランキャッシュに格納します。

obclient> SELECT c1, c2, c3 FROM t1 WHERE c1 = @1 AND c2 LIKE @2 ORDER BY 3;

ユーザーが再度Q2クエリコマンド SELECT c1, c2, c3 FROM t1 WHERE c1 = 1 AND c2 LIKE 'senior%' ORDER BY 2; を発行すると、高速パラメータ化の結果は次の例のようになり、対応するパラメータ配列は {1，'senior%'，2} です。

obclient> SELECT c1, c2, c3 FROM t1 WHERE c1 = @1 and c2 like @2 ORDER BY @3;

これはQ1クエリの高速パラメータ化後のSQL結果と同じですが、「高速パラメータ化により、パラメータ配列の3番目の要素は数字3でなければならない」という制約条件を満たさないため、その計画とはマッチしません。この場合、Q2はハードパースによって新しい実行計画および制約条件（すなわち「高速パラメータ化により、パラメータ配列の3番目の要素は数字2でなければならない」）を生成し、新しい計画と制約条件をプランキャッシュに追加します。これにより、次回Q1およびQ2を実行する際には、それぞれ正しい実行計画がヒットするようになります。

パラメータ化実行計画の識別ポリシー

OceanBaseデータベースは、Hintを使用してSQL文内のリテラルに対するパラメータ置換を有効または無効にすることもサポートしています。詳細については、CURSOR_SHARING_EXACT Hintを参照してください。

あるSQLリクエストについて、パラメータ化された計画にヒットするか、非パラメータ化された計画にヒットするかを区別するために、以下のポリシーを使用します：

1. cursor_sharingを確認する

   まず、cursor_sharingシステム変数を確認します。cursor_sharingの値が異なれば、同じSQLでも必ず異なる計画が適用されます。cursor_sharingを参照してください。

2. query_sqlを確認する

   cursor_sharing変数だけではパラメータ化計画かどうかを判断できない場合、cursor_sharingがEXACTモードであることを意味し、これはCURSOR_SHARING_EXACTHintによって制御されます。Hintを追加するとquery_sqlの生成に影響が出るため、query_sqlを通じてパラメータ化計画と非パラメータ化計画を区別できます。query_sqlはGV$OB_SQL_AUDITまたはGV$OB_PLAN_CACHE_PLAN_STATビューで確認できます。