性能优化：核心库CPU使用率100%，SQL优化后执行效率提升10000多倍
墨墨导读：某客户一系统早上业务高峰时段RAC数据库两节点CPU使用率接近100%，导致业务响应缓慢，通过分析原因定位SQL完成优化改写后降低CPU的使用率，业务恢复正常。
问题现象
客户一系统在2020年12月15日早上业务高峰时段zCloud监控系统告警数据库RAC两个节点CPU100%，数据库大量会话堆积，致业务系统响应缓慢。
cpu过高原因分析
1. 定位导致cpu使用过高的用户
通过操作系统命令top可以看到cpu使用过高均为user占用而非sys,通过查看进程可以看到排在前面的均为oracle用户的进程，且进程号不断变化，由此可以确定是Oracle前台用户导致CPU使用率过高，接下来我们需要查看数据库会话以确定具体原因。
2. 检查数据库会话定位消耗CPU资源较高的会话及SQL
通过zCloud的活动会话TOP 5 Wait Event可以看到大部分会话的等待集中在ON CPU及latch:cache buffers chains，通过TOP等待事件不难看出CPU过高的原因主要为过多的cbc latch等待造成，进一步通过等待事件旁边的过滤器筛选出对应的活动会话可以发现latch:cache buffers chains等待对应的会话的SQL ID均为cuk5cguanbaqt，即为同一个SQL。由于在没有保存问题时刻的截图，这里我们通过对历史性能的下钻分析截图如下：
可以看到该SQL单次平均执行时间为2分钟多，1小时内执行611次，SQL执行效率较差且SQL执行较频繁导致在同一时间出现大量会话等待cbc latch,且cbc latch的等待进一步导致超高的CPU使用率。
分析SQL的性能瓶颈
我们通过zCloud把SQL中绑定变量替换后在sqlplus中执行SQL，然后查看SQL monitor可以看到该SQL的性能瓶颈主要在于merge join中两部分执行次数均为900万次，也就是说其中相关索引和表被多次访问。
SQL性能优化方法
原始SQL文本如下：
由于应用开发人员SQL写的比较复杂，在公司专家怀晓明老师的协助和支持下对SQL进行了下面的改写优化。
1. 减少相关索引和表的访问次数
通过使用with as将MERGE JOIN中的二部分SQL单独拿出进行了改写，一方面让这二部分SQL对应的相关索引和表只访问一次，另一方面也让SQL的逻辑结构更加明朗。
2. 去除不必要的join
由于order_id是2个left join关联表的主键
此时left join是完全没必要的，所以将left join及对应的表从SQL文本中去除减少1次表关联。
3. 进一步减少c表通过索引扫描的结果集
在with as的第二部分是c表和orf表的关联，在这2个表join中，c表没有过滤条件返回了表中大部分数据，且c表的数据量是百万级的，这部分开销还是有一些大。通过查看统计信息，可以看出with as第一部分中o表通过条件过滤后结果集很小，原始的sql结构为select xxx from o where o.order_id in (xxxx),这里的o.order_id正是来源于子查询最内层的c表。由于c表与orf表关联时返回较多的记录，o表通过条件过滤返回较少的记录，我们通过增加exists条件将o表与c表进行一次关联，通过这样等价改写在c表和orf表关联前就过滤掉大部分数据，减少关联时的结果集。
o表通过条件过滤返回不到2000条数据：
等价改写如下：
最终改写完成的SQL如下：
SQL文本改写后的效果
可以看出优化前SQL单次执行时间为154s，这和zCloud的平均执行时间2分钟一致，改写优化后执行时间为12ms。优化前单次逻辑读为11000多万，改写优化后单次逻辑读为1万多。SQL执行效率提升了10000多倍。
优化前：
优化后：
墨天轮原文链接：https://www.modb.pro/db/43085（复制到浏览器中打开或者点击“阅读原文”立即查看）