OB 运维 | 如何有效使用 outline 功能？

2024-09-14 17:31:11 浏览数 (2)
作者：胡呈清，爱可生 DBA 团队成员，擅长故障分析、性能优化，个人博客：[简书 | 轻松的鱼]，欢迎讨论。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 1000 字，预计阅读需要 3 分钟。

为了防止某些 SQL 的执行计划发生变化，我们通常会创建 outline 来绑定执行计划。但是为什么实际过程中，我们创建 outline 并验证时总会遇到不生效的情况？

阅读本文你将了解：

  1. 如何获取 SQL ID
  2. 如何创建 outline
  3. 如何正确验证 outline 效果

下面以一个实例来演示 outline 的正确使用方法。

1实例演示

我们得知业务中 SELECT count(*) FROM ACT_HI_COMMENT; 这个 SQL 选错索引了，需要指定走 IDX_ACT_HI_COMMENT_02 这个索引。

获取 SQL ID

可以从 gv$sql_audit 中获取 SQL ID。

代码语言：javascript复制
obclient [SYS]> select query_sql,sql_id,svr_ip,plan_id,tenant_id from gv$sql_audit where query_sql like 'SELECT%count�T_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)

此时我们发现一个问题：结果中有两条 SQL（对应两个 SQL ID），区别是一个有分号，一个没分号。哪个是业务下发的 SQL？

答案是没有分号的那个。

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

创建 outline

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

代码语言：javascript复制
-- 创建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)

验证效果

误区

认为可以通过 explain 观察执行计划的方式来验证 outline 的效果是一个误区。explain 无法验证 outline 效果（执行计划不会改变）。

代码语言：javascript复制
obclient [JTZJGL]> explain SELECT count(*) FROM ACT_HI_COMMENTG
*************************** 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 会自动给 SQL 加分号，所以只能在 obclient 客户端中执行 SQL，然后在 gv$plan_cache_plan_stat 视图中查询这个 SQL 对应的 OUTLINE_ID。

OUTLINE_ID 只要不为 -1 就说明生效了，OUTLINE_DATA 中也可以看出使用了我们指定的 IDX_ACT_HI_COMMENT_02 索引：

代码语言：javascript复制
--执行原始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)

2总结

  1. 永远推荐 使用 SQL ID 来创建 outline，因为 SQL 文本即使多一个空格都会导致 outline 不生效。
  2. 通过 gvsql_audit 获取SQL_ID，注意区分业务 SQL 在 gvsql_audit 中不会带有分号。
  3. 创建 outline 时需要登录业务租户，并在对应的 schema 下创建，不能在 sys 用户下创建。

验证 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。
    • 也可以通过 gvplan_cache_plan_explain 查看验证时执行的 SQL 的真实执行计划（先通过 gvsql_audit 获取 tenant_id、ip、port、plan_id 四元素），来查看真实的执行计划，是否和 outline 中定义的 hint 一致。

本文关键字：#OceanBase# #outline#

运维 登录 客户端 索引 sql

0 人点赞