本文为墨天轮社区作者 张 sir 原创作品，记录了日常运维 Oracle 数据库过程中遇到的一个慢 SQL 问题的解决、优化过程，文章内容全面具体、剖析到位，且含有经验总结，分享给各位。
问题景象
这次出问题的数据库比拟非凡，承接的零碎交易要求很高，SQL 根本都是短平快，响应工夫根本不能超过 50ms，某天凌晨的 01:12-01:14 在进行压力测试的时候，忽然呈现短暂的交易提早变长的状况，有局部交易超时。利用定位到是数据库返回慢了，要求咱们排查问题。
问题剖析：
步骤一：come on v$ASH
剖析这种问题，我是特地喜爱用 v$active\_session\_history 视图的，尽管 oracle 也提供了 ASH 报告的性能，然而总感觉报告提供的内容太多，没法抓住重点。间接查视图，想看什么信息都能够。首先先看看问题时段整体的数据库 sql 的执行状况，SQL 执行工夫最长曾经到 40s 左右了，这个工夫必定是无奈承受的，而且基本上都是两条 SQL：cuw23huyg926x 和 84m7xzxz0181g。
既然找到了慢 SQL，那就看看他们的次要的期待事件吧，通过上面的查问能够看出 84m7xzxz0181g 次要期待事件是 enq: US – contention 和 row cache lock，cuw23huyg926x 的次要期待事件是 enq: US – contention。
84m7xzxz0181g：
步骤二：期待事件剖析
1）先简略看下这两个期待事件哈：
row cache lock 期待事件是一个共享池相干的期待事件，是因为对字典缓冲的拜访造成的。每一个行缓冲队列锁都对应一个特定的数据字典对象，这被叫做队列锁类型，并能够在 V$ROWCACHE 视图中找到。在 AWR 中须要查看 Dictionary Cache Stats 局部用以确定问题。常见的起因有如下几点：
① 序列没有设置 CACHE 属性，导致序列争用。
② 表空间有余引起 表空间的扩大速度跟不上表空间的应用速度会产生该期待事件。
③ Shared Pool 有余，须要减少共享池。
④ 用户明码谬误或给出了空明码并且频繁登录。
enq: US – contention: 这个 event 阐明事务在队列中期待 UNDO Segment，通常是因为 UNDO 空间有余导致的。
在对此事件阐明之前，须要了解在应用 AUM（atuomatic undo management）时，回滚段在何时联机或脱机。AUM 与 RBU（rollback segment management）不同，回滚段的治理是 Oracle 主动实现的。应用 AUM 时，回滚段的联机或脱机的时刻如下：
1）在执行 alter database open 的时候将回滚段联机
2）通过 alter system set undo_tablespace=xxx 批改撤销表空间时，将原来的回滚段脱机后，再将新的回滚段联机。
3）通过 SMON，主动脱机或者联机回滚段，如果一段时间内，事务量减少，联机状态的回滚段也会减少，一段时间内若是没有实物或事务缩小，回滚段就会被 smon 过程脱机。
为了同步将回滚段联机或脱机的过程，执行该工作的服务器过程或后盾过程应取得 US 锁，每个回滚段非配一个 US 锁，ID1=Undo segment#。若在取得 US 锁的过程中产生争用，则期待 enq：US-contention 事件。服务器过程应该在开始事务时调配到回滚段，但如果不存在可用的回滚段时，应该创立新的回滚段或将脱机状态的回滚段联机。在实现此项工作期间，服务器过程为了取得 US 锁而期待，期待占有可用回滚段。
2）第一个期待事件是跟共享池相干的期待事件，咱们能够通过 v$ash 看看具体期待的字典缓冲类型
3）看一下期待事件 row cahce lock 期待的对象是什么，row cache lock 期待事件的 P1 参数为 cache id，依据 cache id 找到 dc, 能够看到大部分是期待获取 dc\_rollback\_segments，这个等待时间也是在期待获取 undo 信息。
4）第一个期待事件是字典缓冲的争用，争用的对象是 rollback segments，第二个期待事件 enq: US – contention 也是对于 undo segment 的期待。依据以上状况能够看出，row cache lock 和 enq:U – contention 是有相关性的，都是因为获取 undo 的时候产生的争用。然而这个零碎其实是采纳的分库分表的架构，有多个对等角色的数据库，每套数据库交易量基本一致，那为什么这套库有问题，而别的库没问题呢？
步骤三、持续 AWR+v$ASH
以上问题也是我本人的疑难，多套配置雷同的数据库，交易量统一，如果说是因为 undo 引发的，那为啥其余库没问题，单单这套库有问题？感觉问题的本源还不在这里。持续认真查看
awr 报告，发现问题库的 rac 节点 2 的期待事件中有 log file sync 期待，按理说这是一个失常期待，然而其等待时间超过了 2ms，而其余几套库的该期待事件工夫都是几百 us。
再看下 log file parallel write 事件，均匀等待时间是 1.09ms，其余对等数据库的等待时间只有几百 us。
去 V$ASH 里查看这个期待事件的工夫散布状况，发现在 01:12:16s 的时候，log file parallel write 执行超过了 1s。
到这里，基本上能够把证据链梳理下了：
压力测试期间，交易量忽然回升 —–》online 的 undo segment 有余 —–》数据库 online undo segment—–》产生 US 锁争用
而 IO 忽然的梗塞，事务无奈失常提交，加剧了 undo 的争用。针对这个问题，咱们设置了 \_rollback\_segment_count 参数，示意有多少 rollback segment 要处于 online 的状态；能够将该数值设置为数据库最忙碌的时候的回滚段数目。
总结：
1、 如果这个零碎只有一套数据库，剖析到第二步可能就结案了，有同样分库发表的兄弟库做比照，他人都没问题，就你有问题，那阐明还有更深层次的起因。
2、 这种 IO 刹时的提早，基本上无解，尤其对于高并发零碎，一个 IO 抖动就可能导致数据库梗塞。整个存储链路也不可能百分之百的始终处于高质量的状态。
3、 对于从 v$ash 中查到 log file parallel write 有 1s 的提早，这个值的准确性我持狐疑态度，从底层的 os、虚拟化、光纤交换机、存储都没看到这么高的提早。有对这个有钻研的老铁，能够探讨下。
浏览原文：https://www.modb.pro/db/486407
本文为【墨力原创作者打算】征文活动投稿作品，流动收录了数百篇 Oracle、MySQL、PostgreSQL 以及国产数据库相干的文章，蕴含数据库装置配置、性能调优、故障解决、高可用搭建等主题，此外也有 K8s、Java、VUE 等优质稿件。大家 点击此处 可查看有所技术文章。更多 数据库故障解决文章 能够 点击此处 查看。
【墨力原创作者打算】流动长期进行中，首次参加流动即有机会取得定制护腰靠枕；参加月更挑战，还能够取得定制 U 盘、罗技鼠标、华为手环、100-300 元现金处分等奖品，期待您的参加！
具体流动规定能够查看：https://www.modb.pro/db/513210