OceanBase 计划绑定

2021-06-30 11:22 更新

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

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

Outline 视图-gv$outline

Outline 视图为 gv$outline,其参数说明如下:

字段名称

类型

描述

tenant_id

bigint(20)

租户 ID。

database_id

bigint(20)

数据库 ID。

outline_id

bigint(20)

Outline ID。

database_name

varchar(128)

数据库名称。

outline_name

varchar(128)

Outline 名称。

visible_signature

varchar(32768)

Signature 的反序列化结果,为了便于查看 Signature 的信息。

sql_text

varchar(32768)

创建 Outline 时,在 on clause 中指定的 SQL。

outline_target

varchar(32768)

创建 Outline 时,在 to clause 中指定的 SQL。

outline_sql

varchar(32768)

具有完整 Outline 信息的 SQL。

创建 OUTLINE

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

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

使用 SQL_TEXT 创建 Outline

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

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

obclient>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。

注意 
在使用 target_stmt 时,严格要求 stmt 与 target_stmt 在去掉 Hint 后完全匹配。

示例如下:

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)

优化器选择了走主键扫描,如果数据量增大,如果执行索引 idx_c2,该 SQL 会更优化。此时可以通过创建 Outline 将该 SQL 绑定索引计划并执行。

根据如下 SQL 语句,创建 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 创建 Outline

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

obclient>CREATE OUTLINE outline_name ON sql_id USING HINT  hint_text;

说明如下:

  • sql_id 为需要绑定的 SQL 对应的 sql_idsql_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 (0.01 sec)

确定 Outline 创建生效

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

  1. 确定是否创建 Outline 成功。

    通过查看 gv$outline 中的表,确认是否成功创建对应的 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 执行是否通过绑定的 Outline 生成了新计划。

    当绑定 Outline 的 SQL 有新的流量查询后,查询 gv$plan_cache_plan_stat 表中该 SQL 对应的计划信息中 outline_id。如果 outline_id 是在 gv$outline 中查到的 outline_id 则表示该计划是按绑定的 Outline 生成的执行计划,否则不是。

    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. 确定生成的执行计划是否符合预期。

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

    实时执行计划展示

    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 后,对应 SQL 重新生成计划时将不再依据绑定的 Outline 生成。删除 Outline 的语法如下:

DROP OUTLINE outline_name;
注意 
删除 Outline 需要在 outline_name 中指定 Database 名,或者在 USE DATABASE 命令后执行。

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

  • 使用 SQL_TEXT 创建 Outline 后,SQL 请求生成新计划查找 Outline 使用的 Key 与计划缓存使用的 Key 相同,均是 SQL 参数化后的文本串。

  • 当创建和删除 Outline 后,对应 SQL 有新的请求时,会触发执行计划缓存中对应执行计划失效,更新为绑定的 Outline 生成的执行计划。

以上内容是否对您有帮助:
在线笔记
App下载
App下载

扫描二维码

下载编程狮App

公众号
微信公众号

编程狮公众号