OB SQL 性能抖动问题分析和应对

在 OB 的日常运维支持工作中，有一类性能问题场景是 SQL 性能抖动。就是同样的 SQL 其性能不稳定，时好时坏，通常我们关注它性能变坏的情形，称之为性能衰减。SQL 性能抖动并不是 OB 才有的问题，所有关系型数据库都有这个现象。业务通常也能感知到，但让运维查原因却不是那么容易，因为原理细节隐藏极深，且各不相同。下面由简到繁，一一解释。首先 SQL 性能抖动跟 SQL 中具体的查询条件有关。同样的 SQL ，每次条件不一样，读取的数据量、返回的数据量都不完全一样，SQL 性能自然就可能不一样。这点说开了绝大部分人都能理解接受。平常几毫秒到几十毫秒的抖动业务感知不大，但是如果抖动的水平到达秒级别，业务人员往往就失去了主意，开始胡乱猜测了。业务人员虽然不懂数据库，但这种猜测也并非毫无道理。在生产上就有一类故障时因为查询条件的变化导致性能继续恶化。比如说原本的表连接的算法 NESTED-LOOP JOIN 下 SQL 性能很好。结果由于内部表传了一个占比很大的具体值导致符合条件的内部表记录数非常多，导致外部表被循环次数非常大，加上外部表定位记录的性能本就不是很好，整体上这个 SQL 性能就可能从几十毫秒飙升到几十秒了，从而引起数据库性能雪崩。这是数据库的修罗场。（当然说句题外话，OB 的 SQL 查询超时机制就是预防这种的，默认是 10秒超时，可以个改小，防止数据库雪崩用的。偏偏有很多 OB 客户为了省事还要把这个参数调大，如100秒、86400 秒。所以，参数没有完美的。）
第二 SQL性能抖动还跟数据库 SQL 引擎的能力有一定关系，具体一点就是SQL 执行计划机制。SQL 引擎能力越差，这个现象反而越少；SQL 引擎越“智能”，这个现象反而越多。SQL 执行计划基本上所有数据库都有，但是执行计划缓存不一定每个数据库都有。比如说 MySQL 就没有。所以 MySQL 每个 SQL 都要解析执行计划。同样的 SQL ，除了条件中的常量值不一样外，每次都要解析，是否执行计划每次都一样呢。这个答案是不一定。执行计划生成规则有 RBO 和 CBO。如果是 RBO 占主导，那同样的 SQL 跑一百次生成的执行计划大概率也是相同的。但是如果是 CBO 主导，情况就复杂了。查询的条件、表的统计信息都可能会发生变化。就像古希腊哲学家说人不可能两次踏进同一条河流一样，SQL 执行多次，每次的执行环境也可能发生了变化。加上每次都要生成执行计划，导致执行计划变化的概率很大，所以 MySQL SQL 性能理论上会有抖动。不过业务实际体感也不是那么明显，因为 MySQL 的 CBO 能力不够复杂，RBO 在执行计划中影响比例也不小。
再看 ORACLE，SQL引擎能力非常优秀（SQLServer、DB2 能力跟 ORACLE 也差不多）。CBO 会考虑查询条件、表和列的统计信息，包括列的直方图信息、数据库的CPU、IO 负载等外部因素等。所以，同样的 SQL（查询条件不同）在 ORACLE 里多次运行时是可能导致执行计划变化，或者说是可能存在多个版本的执行计划并存。并存指的的是都缓存到执行计划里了。ORACLE 能做到不同的查询条件选择不同的执行计划，这个叫绑定变量窥探技术，其结果有好有坏。符合用户期望的就是好，不符合用户期望的就是不好。表现在业务层面就是 SQL 性能抖动。这些还只是 ORACLE 11g 版本就有的技术。除了 ORACLE 自己能说自己这个技术还不够好外，其他人还真没啥指责的资格。再看 OceanBase，SQL 引擎能力就是以 ORACLE 为目标的（即使兼容 MySQL 租户，也只是语法兼容 MySQL，执行计划方面跟 MySQL 没有一点关系）。OB 的 SQL 默认都会参数化处理，这点跟 ORACLE 不一样。ORACLE 默认是期望用户 SQL 使用绑定变量的。具体参考 ORACLE 的 cursor_sharing 参数设置和原理。OB 也有这个参数。我们这里说的都是默认值行为。OB 对参数化后的 SQL 生成执行计划并缓存，生成执行计划时也会考虑查询条件、表和列的统计信息、负载等外部因素等。结果就是同样的 SQL 如果多次执行在 OB 里也是可能有多个执行计划的。但是跟 ORACLE 不同的是，当 SQL 执行计划已经缓存了，触发 OB 再次生成执行计划的场景是什么。这里我并不确认所有的场景，目前确切知道的场景之一就是如果 OB 在 SQL 复用一个执行计划后执行性能跟这个 SQL 之前的平均执行性能差异很大的时候，OB 就会将原执行计划给作废（失效，日志关键字 plan is expired），并生成新的执行计划替换原有的执行计划。也有可能这个新的执行计划跟原有的执行计划其实是一样的。重新生成只是给了数据库一个重新选择执行计划的机会，这有点碰运气。赌对了，啥事都没发生；赌错了，数据库性能进一步恶化。当然很快 OB 又会再赌一次。相比前面描述的那种场景，这个也算是一个自救的方案，我把它称之为：自愈。（这里也说一句题外话，如果运维觉得数据库这个不靠谱，那运维自己写程序去监测数据库 SQL 性能变化，强行干预执行计划生成机制。这就是运维层面提到的工具的自愈能力的根本原理。）以上都是理论分析，如果没有实证的话，完全可以被认为是胡说八道。好消息是好的数据库都有比较好的可观测性能力。可观测性在 MySQL、ORACLE 、OB 里都有，只是能力不一。很显然我这个排序就代表了这三家数据库在这个能力上的水平了。
最差的就是 MySQL。先撇开 Performance Schema 这个后来才有的设计，MySQL 里没有 SQL 执行计划缓存，没有 SQL 执行信息统计视图。MySQL 会记录慢 SQL 到日志或表 slow_log，MySQL 没有全量 SQL 记录能力。启用 Performance Schema 后，MySQL 可以按一些维度去统计 SQL 的执行性能，比如说 SQL 的平均执行时间、读写行数、最大时间、等待时间、排序行数等等，信息非常丰富。其用意非常好，在 MySQL 里算是一个很好的解决方案，也是 MySQL 数据库三方监控产品性能监控的使用基础。不过由于欠缺执行计划缓存，这个能力跟 ORACLE 比起来还是差的远了。ORACLE 有 SQL 执行计划缓存，对应的执行计划内容、SQL 执行统计信息在视图里都有详细的记录。并且 ORACLE 还有会话和 SQL 执行的历史统计信息视图。此外，ORACLE 后来还有 SQL_MONITOR 视图可以查看慢 SQL 以及被特别标注了 SQL。ORACLE 也没有全量 SQL 记录能力。推测是 ORACLE 的 SQL 诊断理念是关注执行计划、关注执行统计信息（各个指标的平均值和最大值等），以及一些执行时间过长的慢 SQL。平时运维的时候，如果一个 SQL 的平均执行时间上涨了，那表示最近跑了一个慢 SQL 拉高了平均水平；反之，最近跑了一个很快的 SQL，挽救了一下 SQL 的平均性能。统计学家不怎么关注极端值，SQL MONITOR 视图算是 ORACLE 挽救一下这个设计。二者之间并不能确切的形成一对一的联系，你只能推测可能是这些 SQL 参与拉高了 SQL 的平均执行时间。此外，依然不知道是什么 SQL 拉低了平均值小时间（当然不知道没关系，没有人关注这个）。此外 ORACLE 还有 AWR 快照设计，能将会话、SQL 执行计划缓存等打快照保存。这是一个亮点。
再说 OB ，OB 的亮点就在于上面说的 ORACLE 比较好的设计 OB 都有（当然没有提到等待事件这个优秀的设计，这个 OB 还不行），ORACLE 没有的全量 SQL 视图 OB 也有。OB 可以查询定位数据库跑过的所有 SQL。当然不是持久化保存，是都在内存虚拟表（一个 FIFO 的内存对象）里，能保存多少取决于你给多大的内存。OB 这个 SQL 全量审计对性能的影响不高（据说3%的性能），所以生产环境默认开启，就算你生产的 OB 负载很大。设计虽然简单，但是功效却非同寻常。OB 里可以精确的定位到每一笔业务 SQL 的性能数据，借此 OCP 就可以做 SQL的性能抖动监测（实际上用户关注的是性能衰减）。
当然如何从大量 SQL 里快速发现执行性能偏离基线的异常 SQL，这个就是 OCP 的能力了。推测用到一些机器学习的技术。如果没有 OCP 我还不一定能很好的展示这个。首先 OCP 的告警里会给出 SQL巡检告警规则，有两个子类：性能下降、（执行）计划恶化。这是两个视角。前者是说某类 SQL 性能下降了。其原因可能是执行计划变了，也可能是执行计划没变但是是数据变了。后者就是明确发现执行计划变了。这两类在实际情况下都存在。

这两类告警信息都给出了具体的 SQLID ，所以我们分析研究的方法都是一样的。为了研究步骤有点多，熟练后不是每一步都要看。

然后，查询这个 SQLID 跑过的最近的 SQL，观察其性能变化信息。包括 SQL执行时间、读写行数、执行计划信息（SERVER ID,PLAN ID）等等。

select usec_to_time(request_time) req_time, svr_ip, plan_id, ret_code, sql_id,is_hit_plan, elapsed_time, event ,return_rows + affected_rows total_rows
from oceanbase.`GV$OB_SQL_AUDIT`
where tenant_id=1002 and sql_id in ('44846BDCEAC4B544B656075713EE397D')order by request_time desc limit 100;

上面这个截图是我发现最极端的例子。SQL 频繁的换执行计划。具体原因还真不好说，因为它有时候执行时间差异并不大或者执行时间变化大的时候它又没换执行计划。估计 OB 里执行计划触发规则还有更多的细节。这个不重要，重要的是我们观察到了这种执行计划频繁替换的现象。

然后查询这个 SQLID 的执行统计信息。这个跟 ORACLE 的视图就很类似了。（别太在意我这些 SQL 中的 SQLID 跟图中可能不一样，主要是我记录的时机不一样，案例整理比较费时，文章里逻辑做不到绝对严谨）

select svr_ip, plan_id, sql_id, first_load_time, last_active_time, avg_exe_usec, slowest_exe_time, slowest_exe_usec,executions, slow_count, hit_count, outline_id,outline_data
from oceanbase.`GV$OB_PLAN_CACHE_PLAN_STAT`
where tenant_id=1002 and sql_id in ('44846BDCEAC4B544B656075713EE397D')order by sql_id, first_load_time desc ;

从这里看到数据库执行计划缓存视图里有 2 个执行计划。这又是 OB 有趣的地方。这两笔执行计划有一笔是几天前的（6月28号），经过不严谨的观察发现这个早几天的执行计划后面再也没有使用过（executions不变化，last_active_time也不变）。OB 为什么没有淘汰它。而最近的那个执行计划（7月2日）实际上就是前面看到的频繁的更换执行计划对应的最新的执行计划（观察 plan_id）。所以被它淘汰过很多次的执行计划看不到了。但是那个 28号的执行计划却一直都在。这个不是 ORACLE 里多版本执行计划那个概念。OB 的执行计划视图还没有版本字段（推测这是后期 OB SQL 引擎进一步努力的方向，必须有多版本执行计划，不能以一概全）。至于这两个执行计划内容是否一样，就要看 Outline_data 字段。由于那个被频繁淘汰的执行计划没有缓存，所以运维只能通过不断的刷这个查询，观察 outline_data 是否发生变化来区分这个到底是 执行计划变化场景还是执行计划不变的场景。

最后查询一下当前可见的这个执行计划的详细细节，这个就是查看实际执行计划了。获取实际执行计划需要 4 元组（tenant_id, ip, port, plan_id），一个信息不对就看不到或看错执行计划。

SELECT plan_depth depth, plan_line_id line_id, operator,name,ROWS,cost,property
FROM oceanbase.gv$plan_cache_plan_explain
WHERE (tenant_id, ip, port, plan_id) = (1003, '10.0.0.61', 2882, 57262);

至此，我们掌握了 OCP 告警里 SQL 巡检告警及其分析方法，那最后的措施又该是什么呢。

在上面这个场景了，如果执行计划保持不变，只是单纯的性能下降，这个还好说一些。从全量 SQL 里可以看到是条件不同导致读写的行数不同，或者是当时负载不同（看等待事件 event 列）。OB 有等待事件，只是很多时候意思不好懂。这个好解释，但不好解决。一般处理方法就是更新一下相关表的统计信息，让 OB 重新生成执行计划，或许 OB 下次赌执行计划的时候能不一样。

如果是执行计划变化了，我们就要设法捕捉到前后两次执行计划的细节及其差异，从中分析出性能变化的原理。不过这个说起来简单做起来难。一般有这类问题的 SQL 都是非常复杂的，其执行计划可能有十几行或几十行。分析二者的差异这个非常的耗费精力（费眼睛）。但是一旦你能抓到那个你认为最优的执行计划统计信息，抓到那个 outline_data 的内容。那么临时的一个解决方案可以是用这个 outline_data 的内容绑定这个 SQL_ID 的执行计划。OCP 的告警处理里点开 SQL 详情，也会展示当前的执行计划，旁边还给了一个“绑定执行计划”的按钮。实际上我一点都没用上，我感觉它诱导我去绑定的这个执行计划，并不是最优的那个执行计划（反正我是没看出来）。所以，我实际的处理策略往往是自己去捕获那个最优的执行计划的 Outline_data 。如果捕捉有难度，还有个办法就是将这个 SQL 执行统计视图里的 SQL文本原模原样复制出来放到生产数据库里去解析其执行计划，用 explain extended 具体SQL。输出信息里面也有 outline_data，只要这个执行计划符合期望，就用这个 outline_data 去绑定 SQL 执行计划。下面就是个示例。

create outline big_sql on 'D4E6DC7413F89F2C32CABC23B1B6E955' USING HINT *+ LEADING(@"SEL$5" ("N"@"SEL$5" "TPCC.M"@"SEL$5" )) USE_NL(@"SEL$5" ("TPCC.M"@"SEL$5" ))*/;
SELECT database_name,outline_name,sql_id,outline_content
FROM gv$outline;

最后要说的是，使用 outline 绑定执行计划这个只是临时方案，不一定永久有效。SQL 文本可能些许变化就会生成新的 SQL_ID, 大批量的 SQL 绑定执行计划这个有点像打补丁一样的，绝对不是常态。Outline 的管理也不是很方便。

OB 的全量 SQL 能力使得基于 OB 可以构建 SQL 粒度的性能衰减监控。在开发测试或准生产截断就有条件及时发现业务上的问题 SQL场景，并早做应对方法。在生产阶段，通过 OCP 的 SQL 巡检告警，快速定位到有性能抖动（衰减趋势）的 SQL，及时分析其执行计划详细信息，找到抖动的深层原因。在业务发生重大性能故障的时候，通过 OUTLINE 绑定技术临时解决 SQL 带来的性能问题。

可能有些客户运维说我没有看到上面说的这个告警，那表示你的业务数据库设计还是比较简单或健康的。当业务逻辑复杂多变的时候，业务表上的索引就会非常多，业务SQL会越写越复杂。过多的相似的不合理的索引导致 SQL 执行计划空间复杂度非常大，发生抖动的概率也就很大了。很巧我碰到这类业务了，简单分享一下。文中观点纯属个人观点，如有不当，欢迎留言指出。

更多阅读：

• 阿里数据库性能诊断的利器——SQL全量日志
• OBCE V3 培训实验：SQL优化之执行计划干预