OceanBase 計(jì)劃綁定

但對于已上線的業(yè)務(wù)，如果出現(xiàn)優(yōu)化器選擇的計(jì)劃不夠優(yōu)化時(shí)，則需要在線進(jìn)行計(jì)劃綁定，即無需業(yè)務(wù)進(jìn)行 SQL 更改，而是通過 DDL 操作將一組 Hint 加入到 SQL 中，從而使優(yōu)化器根據(jù)指定的一組 Hint，對該 SQL 生成更優(yōu)計(jì)劃。該組 Hint 稱為 Outline，通過對某條 SQL 創(chuàng)建 Outline 可實(shí)現(xiàn)計(jì)劃綁定。

Outline 視圖－gv$outline

Outline 視圖為 gv$outline，其參數(shù)說明如下：

創(chuàng)建 OUTLINE

OceanBase 數(shù)據(jù)庫支持通過兩種方式創(chuàng)建 Outline，一種是通過 SQL_TEXT (用戶執(zhí)行的帶參數(shù)的原始語句)，另一種是通過 SQL_ID 創(chuàng)建。

使用 SQL_TEXT 創(chuàng)建 Outline

使用 SQL_TEXT 創(chuàng)建 Outline 后，會(huì)生成一個(gè) Key-Value 對存儲(chǔ)在 Map 中，其中 Key 為綁定的 SQL 參數(shù)化后的文本，Value 為綁定的 Hint。具體參數(shù)化原則，請參見快速參數(shù)化的約束條件內(nèi)容。

使用 SQL_TEXT 創(chuàng)建 Outline 的語法如下：

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

說明如下：

• 指定 OR REPLACE 后，可以對已經(jīng)存在執(zhí)行計(jì)劃進(jìn)行替換。

• 其中 stmt 一般為一個(gè)帶有 Hint 和原始參數(shù)的 DML 語句。

• 如果不指定 TO target_stmt， 則表示如果數(shù)據(jù)庫接受的 SQL 參數(shù)化后與 stmt 去掉 Hint 參數(shù)化文本相同，則將該 SQL 綁定 stmt 中 Hint 生成執(zhí)行計(jì)劃。

• 如果期望對含有 Hint 的語句進(jìn)行固定計(jì)劃，則需要 TO target_stmt 來指明原始的 SQL。

示例如下：

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

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

obclient> EXPLAIN SELECT * FROM t1 WHERE c2 = 1\G;
*************************** 1. row ***************************
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)

優(yōu)化器選擇了走主鍵掃描，如果數(shù)據(jù)量增大，如果執(zhí)行索引 idx_c2，該 SQL 會(huì)更優(yōu)化。此時(shí)可以通過創(chuàng)建 Outline 將該 SQL 綁定索引計(jì)劃并執(zhí)行。

根據(jù)如下 SQL 語句，創(chuàng)建 Outline：

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

使用 SQL_ID 創(chuàng)建 Outline

使用 SQL_ID 創(chuàng)建 Outline 的語法如下：

obclient>CREATE OUTLINE outline_name ON sql_id USING HINT  hint_text;

說明如下：

• sql_id 為需要綁定的 SQL 對應(yīng)的 sql_id。sql_id 可通過以下方式獲?。?/p>

• 查詢 gv$plan_cache_plan_stat 表獲取。

• 查詢 gv$sql_audit 表獲取。

• 通過參數(shù)化的原始 SQL，使用 MD5 生成 sql_id 。可參考如下腳本生成對應(yīng) 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 綁定 Outline，如下例所示：

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

Outline Data 是優(yōu)化器為了完全復(fù)現(xiàn)某一計(jì)劃而生成的一組 Hint 信息，以BEGIN_OUTLINE_DATA開始，并以 END_OUTLINE_DATA結(jié)束。

Outline Data 可以通過 EXPLAIN EXTENDED 命令獲得，如下例所示：

obclient>EXPLAIN EXTENDED SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1\G;
*************************** 1. row ***************************
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))

其中 Outline Data 信息如下例所示：

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

Outline Data 也是 Hint，因此可以用在計(jì)劃綁定的過程中，如下例所示：

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 (0.01 sec)

確定 Outline 創(chuàng)建生效

確定創(chuàng)建的 Outline 是否成功且符合預(yù)期，需要進(jìn)行如下三步的驗(yàn)證：

1. 確定是否創(chuàng)建 Outline 成功。

   通過查看 gv$outline 中的表，確認(rèn)是否成功創(chuàng)建對應(yīng)的 Outline 名稱的 Outline。

   obclient> SELECT * FROM oceanbase.gv$outline 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 執(zhí)行是否通過綁定的 Outline 生成了新計(jì)劃。

   當(dāng)綁定 Outline 的 SQL 有新的流量查詢后，查詢 gv$plan_cache_plan_stat 表中該 SQL 對應(yīng)的計(jì)劃信息中 outline_id。如果 outline_id 是在 gv$outline 中查到的 outline_id 則表示該計(jì)劃是按綁定的 Outline 生成的執(zhí)行計(jì)劃，否則不是。

   obclient>SELECT SQL_ID, PLAN_ID, STATEMENT, OUTLINE_ID, OUTLINE_DATA 
         FROM oceanbase.gv$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. 確定生成的執(zhí)行計(jì)劃是否符合預(yù)期。

   確定是通過綁定的 Outline 生成的計(jì)劃后，需要確定生成的計(jì)劃是否符合預(yù)期，可以通過查詢gv$plan_cache_plan_stat 表查看 plan_cache 中緩存的執(zhí)行計(jì)劃形狀，具體查看方式可參考

   實(shí)時(shí)執(zhí)行計(jì)劃展示。

   obclient>SELECT OPERATOR, NAME FROM oceanbase.gv$plan_cache_plan_explain 
         WHERE TENANT_ID = 1001 AND IP = '10.101.163.87' 
          AND PORT = 30474 AND PLAN_ID = 17225;
   
   +--------------------+------------+
   | OPERATOR           | NAME       |
   +--------------------+------------+
   |  PHY_ROOT_TRANSMIT | NULL       |
   |   PHY_TABLE_SCAN   | t1(idx_c2) |
   +--------------------+------------+

刪除 Outline

刪除 Outline 后，對應(yīng) SQL 重新生成計(jì)劃時(shí)將不再依據(jù)綁定的 Outline 生成。刪除 Outline 的語法如下：

DROP OUTLINE outline_name;

計(jì)劃綁定與執(zhí)行計(jì)劃緩存關(guān)系

• 使用 SQL_TEXT 創(chuàng)建 Outline 后，SQL 請求生成新計(jì)劃查找 Outline 使用的 Key 與計(jì)劃緩存使用的 Key 相同，均是 SQL 參數(shù)化后的文本串。

• 當(dāng)創(chuàng)建和刪除 Outline 后，對應(yīng) SQL 有新的請求時(shí)，會(huì)觸發(fā)執(zhí)行計(jì)劃緩存中對應(yīng)執(zhí)行計(jì)劃失效，更新為綁定的 Outline 生成的執(zhí)行計(jì)劃。