分布式数据库学习Note81：OceanBase社区版中，如何实现计划绑定？

分布式数据库爱好者 2023-02-13

608

通过对某条 SQL 创建 Outline 可实现计划绑定。

在系统上线前，可以直接在 SQL 语句中添加 Hint，控制优化器按 Hint 指定的行为进行计划生成。

但对于已上线的业务，如果出现优化器选择的计划不够优化时，则需要在线进行计划绑定，即无需业务进行 SQL 更改，而是通过 DDL 操作将一组 Hint 加入到 SQL 中，从而使优化器根据指定的一组 Hint，对该 SQL 生成更优计划。该组 Hint 称为 Outline。

Outline 视图

Outline 视图为 DBA_OB_OUTLINES，其字段说明如下表所示。

字段名称	类型	描述
GMT_CREATE	TIMESTAMP(6)	创建时间戳
GMT_MODIFIED	TIMESTAMP(6)	修改时间戳
TENANT_ID	BIGINT(20)	租户 ID
DATABASE_ID	BIGINT(20)	数据库 ID
OUTLINE_ID	BIGINT(20)	Outline ID
DATABASE_NAME	VARCHAR2(128)	数据库名称
OUTLINE_NAME	VARCHAR2(128)	Outline 名称
VISIBLE_SIGNATURE	LONGTEXT	Signature 的反序列化结果，为了便于查看 Signature 的信息。
SQL_TEXT	LONGTEXT	创建 Outline 时，在 `ON` 子句中指定的 SQL。
OUTLINE_TARGET	LONGTEXT	创建 Outline 时，在 `TO` 子句中指定的 SQL。
OUTLINE_SQL	LONGTEXT	具有完整 Outline 信息的 SQL
SQL_ID	VARCHAR2(32)	SQL 标识符
OUTLINE_CONTENT	LONGTEXT	完整的执行计划 Outline 信息

创建 Outline

OceanBase 数据库支持通过两种方式创建 Outline，一种是通过 SQL_TEXT (用户执行的带参数的原始语句)，另一种是通过 SQL_ID 创建。

注意
创建 Outline 需要进入对应的数据库下执行。

使用 SQL_TEXT 创建 Outline

使用 SQL_TEXT 创建 Outline 后，会生成一个 Key-Value 对存储在 Map 中，其中 Key 为绑定的 SQL 参数化后的文本，Value 为绑定的 Hint。具体参数化原则，请参见快速参数化。

使用 SQL_TEXT 创建 Outline 的语法如下：

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 的语句执行固定计划，则需要 TO target_stmt 来指明原始的 SQL。
示例如下：
```
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 会更优化。此时可以通过创建 Outline 将该 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\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)

根据如下 SQL 语句创建 Outline：

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

使用 SQL_ID 创建 Outline

使用 SQL_ID 创建 Outline 的语法如下：

obclient> CREATE OUTLINE outline_name ON sql_id USING HINT hint_text;

SQL_ID 为需要绑定的 SQL 对应的 SQL_ID，可以通过以下方式获取：

通过查询 gv$plan_cache_plan_stat 获取。
通过查询 gv$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 绑定 Outline，如下例所示：

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

注意
Hint 格式为 /*+ xxx */，关于 Hint 说明的详细信息，请参见 Optimizer Hint。
使用 SQL_TEXT 方式创建的 Outline 会覆盖 SQL_ID 方式创建的 Outline。SQL_TEXT 方式创建的优先级更高。
如果 SQL_ID 对应的 SQL 语句已经有 Hint，则创建 Outline 指定的 Hint 会覆盖原始语句中所有 Hint。

Outline Data 是优化器为了完全复现某一计划而生成的一组 Hint 信息，以 BEGIN_OUTLINE_DATA 开始，并以 END_OUTLINE_DATA 结束。

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，因此可以用在计划绑定的过程中，如下例所示：

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

确定 Outline 创建生效

确定创建的 Outline 是否成功且符合预期，需要进行如下三步的验证：

确定 Outline 创建成功。

通过查看 DBA_OB_OUTLINES 视图，确认是否成功创建对应名称的 Outline。

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

确定新的 SQL 是否通过绑定的 Outline 生成了新执行计划。

当绑定 Outline 的 SQL 执行新的查询后，查询 gv$plan_cache_plan_stat 表中该 SQL 对应的计划信息中的 outline_id。如果 outline_id 与在 DBA_OB_OUTLINES 中查到的 outline_id 相同，则表示是按绑定的 Outline 生成的执行计划，否则不是。

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*/

确定生成的执行计划是否符合预期。

确定是通过绑定的 Outline 生成的计划后，需要确定生成的计划是否符合预期，可以通过查询 GV$OB_PLAN_CACHE_PLAN_STAT 表查看 plan_cache 中缓存的执行计划形状，具体查看方式可参考

实时执行计划展示。

obclient> SELECT OPERATOR, NAME FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT 
      WHERE TENANT_ID = 1001 AND IP = '10.1.1.1' 
       AND PORT = 30474 AND PLAN_ID = 17225;

+--------------------+------------+
| OPERATOR           | NAME       |
+--------------------+------------+
|  PHY_ROOT_TRANSMIT | NULL       |
|   PHY_TABLE_SCAN   | t1(idx_c2) |
+--------------------+------------+

删除 Outline

删除 Outline 后，对应 SQL 将不再依据所绑定的 Outline 重新生成执行计划。

删除 Outline 的语法如下：

DROP OUTLINE outline_name;

注意
删除 Outline 需要在 outline_name 中指定 Database 名，或者在 USE DATABASE 命令后执行删除操作。

计划绑定与执行计划缓存关系

使用 SQL_TEXT 创建 Outline 后，SQL 请求生成新计划查找 Outline 使用的 Key 与计划缓存使用的 Key 是相同的，即均是 SQL 参数化后的文本串。
当创建和删除 Outline 后，对应 SQL 有新的请求时，会触发执行计划缓存中对应执行计划失效，更新为根据绑定的 Outline 所生成的执行计划。

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者