OceanBase:outline使用技巧

为了防止某些SQL的执行计划发生变化，我们通常会创建 outline 来绑定执行计划。但是实际过程中，总会遇到一些绑定后不生效的奇怪问题，下面总结了一些经验技巧，希望可以帮助大家使用 outline 时更顺畅。

实例演示

下面以一个实例来演示为什么我们创建 outline 并验证时总会遇到不生效的情况。
假设我们得知业务中 SELECT count(*) FROM ACT_HI_COMMENT; 这个SQL选错索引了，需要指定走 IDX_ACT_HI_COMMENT_02 这个索引。

1. 获取 SQL ID

第一步取 SQL ID，这个可以从 gv$sql_audit 中获取，这时候我们发现一个问题：有两个 SQL ID，两个SQL的区别是一个有分号，一个没分号。哪个是业务下发的SQL？答案是没有分号的那个：

• 应用程序发起的 SQL 请求，以及 obclient 客户端执行的 SQL ，在 gv$sql_audit 中都是没有分号的
• 在 ODC 中执行的SQL无论加不加分号，在 gv$sql_audit 中都有分号

obclient [SYS]> select query_sql,sql_id,svr_ip,plan_id,tenant_id from gv$sql_audit where query_sql like 'SELECT%count%ACT_HI_COMMENT%';
+-------------------------------------------+----------------------------------+------------+---------+-----------+
| QUERY_SQL                                 | SQL_ID                           | SVR_IP     | PLAN_ID | TENANT_ID |
+-------------------------------------------+----------------------------------+------------+---------+-----------+

| SELECT count(*) FROM ACT_HI_COMMENT;      | AC1ED40EC4D5E1A9D75944216745063A | 26.0.8.170 |   99968 |      1001 |
| SELECT count(*) FROM ACT_HI_COMMENT       | 46815AF386F959D17293BCF931FEEAF1 | 26.0.8.170 |   99798 |      1001 |
+-------------------------------------------+----------------------------------+------------+---------+-----------+
2 rows in set (8.695 sec)

2. 创建 outline

因此我们用第二个SQL ID 来绑定执行计划，注意这里要在对应的 schema 下创建，常见的误区是在 sys 用户下创建，这是不会生效的：

-- 创建outline
conn JTZJGL;
create outline test_outline on '46815AF386F959D17293BCF931FEEAF1' using hint /*+ index(ACT_HI_COMMENT IDX_ACT_HI_COMMENT_02) */;

-- 查询outline
obclient [JTZJGL]> select OUTLINE_ID,DATABASE_NAME,OUTLINE_NAME,OUTLINE_SQL,SQL_ID,OUTLINE_CONTENT from gv$outline;
+------------------+---------------+--------------+-------------+----------------------------------+---------------------------------------------------+
| OUTLINE_ID       | DATABASE_NAME | OUTLINE_NAME | OUTLINE_SQL | SQL_ID                           | OUTLINE_CONTENT                                   |
+------------------+---------------+--------------+-------------+----------------------------------+---------------------------------------------------+
| 1100611139404781 | JTZJGL        | TEST_OUTLINE |             | 46815AF386F959D17293BCF931FEEAF1 | /*+index(ACT_HI_COMMENT IDX_ACT_HI_COMMENT_02) */ |
+------------------+---------------+--------------+-------------+----------------------------------+---------------------------------------------------+
1 row in set (0.005 sec)

3. 验证效果

验证 outline 效果最大的一个误区就是用 explain 来观察执行计划是否生效，这是错误的，explain 无法验证 outline 效果（执行计划不会改变）：

obclient [JTZJGL]> explain SELECT count(*) FROM ACT_HI_COMMENT\G
*************************** 1\. row ***************************
Query Plan: ==========================================================================
|ID|OPERATOR       |NAME                                 |EST. ROWS|COST |
--------------------------------------------------------------------------
|0 |SCALAR GROUP BY|                                     |1        |89615|
|1 | TABLE SCAN    |ACT_HI_COMMENT(IDX_ACT_HI_COMMENT_01)|210872   |81567|
==========================================================================

Outputs & filters: 
-------------------------------------
  0 - output([T_FUN_COUNT(*)]), filter(nil), 
      group(nil), agg_func([T_FUN_COUNT(*)])
  1 - output([1]), filter(nil), 
      access([ACT_HI_COMMENT.TASK_ID_]), partitions(p0)

1 row in set (0.004 sec);

应该执行原始SQL来验证，并且前面我们讲了 ODC 会自动加分号，所以只能在 obclient 客户端中执行SQL，然后在 gv$plan_cache_plan_stat 视图中查询这个SQL对应的 OUTLINE_ID，只要不为 -1 就说明生效了，OUTLINE_DATA 中也可以看出使用了我们指定的 IDX_ACT_HI_COMMENT_02 索引：

--执行原始SQL
obclient [JTZJGL]> SELECT count(*) FROM ACT_HI_COMMENT;
+----------+
| COUNT(*) |
+----------+
|   210872 |
+----------+
1 row in set (0.110 sec)

--然后在gv$plan_cache_plan_stat视图中查询这个SQL对应的 OUTLINE_ID，只要不为 -1 就说明生效了
obclient [SYS]> select SQL_ID,PLAN_ID,QUERY_SQL,OUTLINE_ID,OUTLINE_DATA from gv$plan_cache_plan_stat where sql_id='46815AF386F959D17293BCF931FEEAF1';
+----------------------------------+---------+-------------------------------------+------------------+-------------------------------------------------------------------------------------------------------------------+
| SQL_ID                           | PLAN_ID | QUERY_SQL                           | OUTLINE_ID       | OUTLINE_DATA                                                                                                      |
+----------------------------------+---------+-------------------------------------+------------------+-------------------------------------------------------------------------------------------------------------------+
| 46815AF386F959D17293BCF931FEEAF1 |   99996 | SELECT count(*) FROM ACT_HI_COMMENT | 1100611139404781 | /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "JTZJGL.ACT_HI_COMMENT"@"SEL$1" "IDX_ACT_HI_COMMENT_02") END_OUTLINE_DATA*/ |
+----------------------------------+---------+-------------------------------------+------------------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.057 sec)

技巧总结

1. 永远推荐使用 SQL ID 来创建 outline，因为SQL 文本即使多一个空格都会导致 outline 不生效，通过 gv$sql_audit 获取SQL_ID，注意区分业务SQL在 gv$sql_audit 中不会带有分号
2. 创建 outline 时需要登录业务租户，并在对应的 schema 下创建，不能在 sys 用户下创建
3. 验证 outline 时：
   • explain 看执行计划是不会改变的，不能用来验证 outline 是否生效
   • 执行原始SQL时：
     • 执行的SQL文本需要从 gv$sql_audit 获取，并且不能有任何修改，不能美化 SQL 后执行
     • 不能使用 ODC 执行SQL，因为 ODC 会给SQL加上分号或者改写SQL，这样会导致 SQL ID 变化，无法命中 outline
   • 执行原始SQL后：
     • 可以通过 gv$plan_cache_plan_stat 来找到目标SQL的 plan cache 状态，如果 outline_id 不为 -1，则说明命中了outline
     • 也可以通过 gv$plan_cache_plan_explain 查看验证时执行的 SQL 的真实执行计划（先通过gv$sql_audit获取tenant_id、ip、port、plan_id四元素），来查看真实的执行计划，是否和 outline 中定义的 hint 一致