実行計画のバインディング

特定のSQL文にアウトラインを作成することで、計画のバインディングを実現できます。

システムが本番稼働する前に、SQL文に直接ヒント（Hint）を追加し、オプティマイザーがそのヒントに従って計画を生成するように制御できます。

しかし、本番稼働中の業務において、オプティマイザーが選択した計画が十分に最適化されていない場合は、オンラインで計画のバインディングを行う必要があります。つまり、業務側でSQL文を変更することなく、DDL操作を通じて一連のヒントをSQL文に追加することで、オプティマイザーが指定されたヒントに基づいてより優れた計画を生成します。この一連のヒントをアウトラインと呼びます。

アウトラインの作成

OceanBaseデータベースでは、2つの方法でアウトラインを作成できます。1つはSQL_TEXT(ユーザーが実行するパラメータ付きの生のステートメント)を使用して作成する方法、もう1つはSQL_IDを使用して作成する方法です。

SQL_TEXTを使用したアウトラインの作成

SQL_TEXT を使用してアウトラインを作成すると、Key-Valueペアが生成されてMapに格納されます。ここで、KeyはバインドされたSQLパラメータ化後のテキスト、Valueはバインドされたヒントです。具体的なパラメータ化の原則については、クイックパラメータ化を参照してください。

SQL_TEXT を使用してアウトラインを作成する構文は以下のとおりです：

CREATE [OR REPLACE] OUTLINE <outline_name> ON <stmt> [ TO <target_stmt> ];

説明は以下のとおりです：

• OR REPLACE を指定すると、既存の実行計画を置き換えることができます。

• ここで、stmt は一般的にヒントと元のパラメータを含むDMLステートメントです。

• TO target_stmt を指定しない場合、データベースが受け入れるSQLパラメータ化後のテキストが stmt のヒントパラメータ化テキストと同じ場合、そのSQLは stmt 内のヒントにバインドされた実行計画を生成します。

  ヒントを含むステートメントに対して固定計画を実行したい場合は、元のSQLを指定するために TO target_stmt が必要です。

  例：

  obclient> CREATE OUTLINE outline1 ON SELECT /*+NO_REWRITE*/ * FROM tbl1 WHERE col1 = 4
  AND col2 = 6 ORDER BY 2 TO SELECT  * FROM tbl1 WHERE col1 = 4 AND col2 = 6 ORDER BY 2;
  

  注意

  target_stmt を使用する際は、stmt と target_stmt がヒントを除いた後に完全に一致することが厳密に求められます。

次の例では、オプティマイザーは主キースキャンを選択しました。データ量が増加した場合、インデックス idx_c2 を実行すると、このSQLはより最適化されます。この時点で、アウトラインを作成してこのSQLをインデックス計画にバインドし実行することができます。

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

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

obclient> EXPLAIN SELECT * FROM t1 WHERE c2 = 1;

Query Plan:
===================================
|ID|OPERATOR  |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1  |1        |37  |
===================================
Outputs & filters:
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 = 1]),
      access([t1.c2], [t1.c1], [t1.c3]), partitions(p0)

次のSQLステートメントに基づいてアウトラインを作成します：

obclient> CREATE OUTLINE otl_idx_c2
       ON SELECT/*+ INDEX(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1;
Query OK, 0 rows affected

SQL_IDを使用したアウトラインの作成

SQL_ID を使用してアウトラインを作成する構文は次のとおりです：

obclient> CREATE OUTLINE outline_name ON sql_id USING HINT hint_text;

SQL_ID とは、バインドする必要があるSQLに対応する SQL_ID のことであり、以下の方法で取得できます：

• V$OB_PLAN_CACHE_PLAN_STAT を照会することで取得します。

• GV$OB_SQL_AUDIT を照会することで取得します。

• パラメータ化された元のSQLを使用して、MD5を用いて SQL_ID を生成します。以下のスクリプトを参照して、対応するSQLの SQL_ID を生成できます。

  IMPORT hashlib
  sql_text='SELECT * FROM t1 WHERE c2 = ?'
  sql_id=hashlib.md5(sql_text.encode('utf-8')).hexdigest().upper()
  PRINT(sql_id)
  

SQL_ID を使用してアウトラインをバインドするには、次の例のようにします：

obclient> CREATE OUTLINE otl_idx_c2 ON 'ED570339F2C856BA96008A29EDF04C74'
USING HINT /*+ INDEX(t1 idx_c2)*/ ;

アウトラインデータとは、オプティマイザーが特定の計画を完全に再現するために生成される一連のHint情報であり、BEGIN_OUTLINE_DATA で開始し、END_OUTLINE_DATA で終了します。

アウトラインデータは、EXPLAIN EXTENDED コマンドを使用して取得できます。例：

obclient> EXPLAIN EXTENDED SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1;

Query Plan:
| =========================================
|ID|OPERATOR  |NAME      |EST. ROWS|COST|
-----------------------------------------
|0 |TABLE SCAN|t1(idx_c2)|1        |88  |
=========================================

Outputs & filters:
-------------------------------------
  0 - output([t1.c1(0x7ff95ab37448)], [t1.c2(0x7ff95ab33090)], [t1.c3(0x7ff95ab377f0)]), filter(nil),
      access([t1.c2(0x7ff95ab33090)], [t1.c1(0x7ff95ab37448)], [t1.c3(0x7ff95ab377f0)]), partitions(p0),
      is_index_back=true,
      range_key([t1.c2(0x7ff95ab33090)], [t1.c1(0x7ff95ab37448)]), range(1,MIN ; 1,MAX),
      range_cond([t1.c2(0x7ff95ab33090) = 1(0x7ff95ab309f0)])

Used Hint:
-------------------------------------
  /*+
      INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2")
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2")
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
LOCAL

Optimization Info:
-------------------------------------

t1:table_rows:3, physical_range_rows:1, logical_range_rows:1, index_back_rows:1, output_rows:1, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_c2], pruned_index_name[t1]
level 0:
***********
   paths(@1101710651081553(ordering([t1.c2], [t1.c1]), cost=87.951827))

その中のアウトラインデータ情報は次のとおりです：

  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2")
      END_OUTLINE_DATA
  */

アウトラインデータもHintに属するため、計画のバインディングプロセスで使用できます。例：

obclient> CREATE OUTLINE otl_idx_c2
     ON 'ED570339F2C856BA96008A29EDF04C74'
      USING HINT /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2")
      END_OUTLINE_DATA
  */;
Query OK, 0 rows affected

アウトラインの作成が正常に完了したかどうかを確認する

作成したアウトラインが正常に作成され、期待通りであるかどうかを確認するには、以下の3つのステップの検証が必要です：

1. アウトラインの作成が成功したかどうかを確認します。

   DBA_OB_OUTLINESビューを確認して、対応する名前のアウトラインが正常に作成されたかどうかを確認します。

   obclient> SELECT * FROM DBA_OB_OUTLINES WHERE OUTLINE_NAME = 'otl_idx_c2'\G
   
   *************************** 1. row ***************************
           tenant_id: 1001
         database_id: 1100611139404776
          outline_id: 1100611139404777
       database_name: test
        outline_name: otl_idx_c2
   visible_signature: SELECT * FROM t1 WHERE c2 = ?
            sql_text: SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1
      outline_target:
         outline_sql: SELECT /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2") END_OUTLINE_DATA*/*
   FROM t1 WHERE c2 = 1
   

2. 新しいSQLがバインドされたアウトラインによって新しい実行計画を生成したかどうかを確認します。

   アウトラインにバインドされたSQLが新しいクエリを実行した後、GV$OB_PLAN_CACHE_PLAN_STATテーブルの該当するSQLの計画情報からoutline_idを照会します。もしoutline_idがDBA_OB_OUTLINESで見つかったoutline_idと同じであれば、バインドされたアウトラインに基づいて生成された実行計画であることを示します。そうでなければ、そうではありません。

   obclient> SELECT SQL_ID, PLAN_ID, STATEMENT, OUTLINE_ID, OUTLINE_DATA
         FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT
          WHERE STATEMENT LIKE '%SELECT * FROM t1 WHERE c2 =%'\G
   *************************** 1. row ***************************
         sql_id: ED570339F2C856BA96008A29EDF04C74
        plan_id: 17225
      statement: SELECT * FROM t1 WHERE c2 = ?
     outline_id: 1100611139404777
   outline_data: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2") END_OUTLINE_DATA*/
   

3. 生成された実行計画が期待通りであるかどうかを確認します。

   バインドされたアウトラインによって生成された計画であることを確認したら、生成された計画が期待通りであるかどうかを確認する必要があります。これは、GV$OB_PLAN_CACHE_PLAN_EXPLAINテーブルを照会してplan_cacheにキャッシュされている実行計画の形状を確認することで行えます。具体的な確認方法については、次のドキュメントを参照してください。

   リアルタイム実行計画表示.

   obclient> SELECT OPERATOR, NAME FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_EXPLAIN
         WHERE TENANT_ID = 1001 AND SVR_IP = '10.XXX.XXX.XXX'
          AND SVR_PORT = 30474 AND PLAN_ID = 17225;
   
   +--------------------+------------+
   | OPERATOR           | NAME       |
   +--------------------+------------+
   |  PHY_ROOT_TRANSMIT | NULL       |
   |   PHY_TABLE_SCAN   | t1(idx_c2) |
   +--------------------+------------+
   

アウトラインの削除

アウトラインを削除すると、対応するSQLは、バインドされたアウトラインに基づいて実行計画を再生成しなくなります。

アウトラインを削除する構文は以下のとおりです：

DROP OUTLINE outline_name;

プランのバインディングと実行計画キャッシュの関係

• SQL_TEXT を使用してアウトラインを作成した後、SQLリクエストが新しいプランを生成する際には、アウトラインを参照するために使用されるキーと、プランキャッシュで使用されるキーが同一になります。つまり、どちらもSQLパラメータ化後のテキスト文字列となります。

• アウトラインの作成または削除後、対応するSQLに新たなリクエストがある場合、実行計画キャッシュ内の該当する実行計画が無効となり、バインドされたアウトラインに基づいて生成された実行計画に更新されます。

関連ドキュメント

• Outlineビュー