性能优化:核心库CPU使用率,SQL优化后执行效率提升10000多倍

2021-01-27 00:00:00 执行 优化 会话 关联 改写


导读:某客户一系统早上业务高峰时段RAC数据库两节点CPU使用率接近,导致业务响应缓慢,通过分析原因定位SQL完成优化改写后降低CPU的使用率,业务恢复正常。


问题现象

客户一系统在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表关联前就过滤掉大部分数据,减少关联时的结果集。

c表通过索引返回了840多万的数据量接近表的数据量900多万:
o表通过条件过滤返回不到2000条数据:

等价改写如下:

终改写完成的SQL如下:

SQL文本改写后的效果

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

优化前:

优化后:


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



相关文章