クイックパラメータ化

パラメータ化とは、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データベース内でパラメータ化処理を行った結果は次のとおりです。定数 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リクエストについて、以下のポリシーを使用して、その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ビューから確認できます。