如何使用 outline 绑定执行计划

通过在 SQL 中添加 HINT 可以控制优化器按 HINT 指定的行为进行计划生成，直接在 SQL 语句中添加 HINT 的方法适用于在系统上线前，对于已上线的业务，如果出现优化器选择的计划不够优时，则需要在线进行计划绑定，通过对某条 SQL 创建 OUTLINE 可达到计划绑定的目的。

创建 OUTLINE

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

使用 SQL_TEXT 创建

使用 SQL_TEXT 创建 OUTLINE 后，会生成一个 key-value 对存储在 map 中，其中 key 为绑定的 SQL参数化后的值，value 为绑定的 HINT。

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

其中：

• 指定 OR REPLACE 后，可以对已经存在的 OUTLINE 进行替换。

• stmt 一般为一个带有 HINT 和原始参数的 DML 语句。

• 如果不指定 TO target_stmt， 则表示如果数据库接受的 SQL 参数化后与 stmt 去掉 HINT 参数化文本相同，则将该 SQL 绑定 stmt 中 HINT 生成执行计划。

• 如果期望对含有 HINT 的语句进行固定计划，则需要 TO target_stmt 来指明原始的 SQL。

  注意

  在使用 TO target_stmt 子句时，要求原始 SQL stmt 与 target_stmt 在去掉 HINT 后完全匹配

使用 SQL_ID 创建

使用 SQL_ID 创建的语法如下所示。

CREATE OUTLINE outline_name ON sql_id USING HINT hint;

sql_id 为需要绑定的 SQL 对应的 SQL_ID。SQL_ID 可通过以下几种方式获取。

• 查询 gv$plan_cache_plan_stat 表获取。

• 查询 gv$sql_audit 表获取。

• 通过参数化的原始 SQL，使用 MD5 生成。

  具体可使用下面这个脚本生成对应 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)
  

删除 OUTLINE

删除 OUTLINE 后，对应 SQL 重新生成计划时将不再依据绑定的 OUTLINE 生成。

删除 OUTLINE 的语法如下。

 obclient> DROP OUTLINE outline_name;

确认 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 有新的流量查询后，查询 (g)v$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. 确定生成的执行计划是否符合预期。

   通过查询 gv$plan_cache_plan_explain 表查看 plan_cache 中缓存的执行计划，查看执行计划是否符合预期。

   SELECT OPERATOR, NAME 
    FROM oceanbase.gv$plan_cache_plan_explain 
    WHERE tenant_id = 1001 AND ip = 'xxx.xxx.xxx.xxx' 
          AND port = 30474 AND plan_id = 17225;
   
   +--------------------+------------+
   | OPERATOR           | NAME       |
   +--------------------+------------+
   |  PHY_ROOT_TRANSMIT | NULL       |
   |   PHY_TABLE_SCAN   | t1(idx_c2) |
   +--------------------+------------+