プランのバインド

OceanBaseデータベースでは、CREATE OUTLINE ステートメントを使用して特定のSQLステートメントと実行計画を結びつけることができます。これにより、そのSQLが以降の実行時に常に指定された実行計画を使用することが保証され、オプティマイザーの選択が不適切なために生じるパフォーマンスの変動を回避できます。つまり、特定のSQLにアウトラインを作成することでプランのバインドを実現でき、システムの本番稼働前にSQLステートメントに直接Hintを追加し、オプティマイザーにHintで指定された動作でプランを生成させることができます。

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

Outlineビュー

OutlineビューはDBA_OB_OUTLINESであり、そのフィールド説明は以下の表のとおりです。

アウトラインの作成

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

SQL_TEXTを使用してアウトラインを作成する

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

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

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

説明は以下の通りです：

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

• ここで、stmt は通常、Hintと元のパラメータを持つDMLステートメントです。

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

  Hintを含むステートメントに対して固定の計画を実行したい場合は、元の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 がHintを除去した後、完全に一致する必要があります。

以下の例では、オプティマイザーは主キースキャンを選択しました。データ量が増加した場合、インデックス 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)*/ ;

アウトラインデータは、オプティマイザーが特定の実行計画を完全に再現するために生成する一連のヒント情報であり、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
  */

アウトラインデータもヒントの一種であるため、計画のバインドプロセスで使用できます。例を以下に示します：

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に新しいリクエストがあると、実行計画キャッシュ内の対応する実行計画が無効になり、バインドされたアウトラインに基づいて生成された実行計画に更新されます。