问题现象

客户一系统在2020年12月15日早上业务高峰时段zCloud监控系统告警数据库RAC两个节点CPU，数据库大量会话堆积，致业务系统响应缓慢。

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表关联前就过滤掉大部分数据，减少关联时的结果集。

终改写完成的SQL如下：

SQL文本改写后的效果

可以看出优化前SQL单次执行时间为154s，这和zCloud的平均执行时间2分钟一致，改写优化后执行时间为12ms。优化前单次逻辑读为11000多万，改写优化后单次逻辑读为1万多。SQL执行效率提升了10000多倍。

优化前：

优化后：

墨天轮原文链接：https://www.modb.pro/db/43085