今天在看崔华老师所著SQL优化一书时,看到他解决SQL性能问题的一个案例,崔华老师成功定位问题并进行了解决。这里,在崔华老师分析定位的基础上,做进一步分析和推理,以便大家一起研究探讨,下面简述该案例场景。
1、发生性能问题的SQL语句:
![e1521b084c78d25e94076f15b63683ff.png](https://i-blog.csdnimg.cn/blog_migrate/94154f6b061bfabec3bed767d025b975.jpeg)
![abdb0087158c411413724dfeb3ebd948.png](https://i-blog.csdnimg.cn/blog_migrate/b08d4f3dc3c01998f52c53a638b7e403.jpeg)
![14df38411d11b8bb99129f26062e865a.png](https://i-blog.csdnimg.cn/blog_migrate/293557a367d4f07b8df799fb143f88ea.png)
![cbaed5689401ec9881f2a0e0a38c63cd.png](https://i-blog.csdnimg.cn/blog_migrate/af93e41260761204ca9ce36f9eb582fa.png)
--注:
1)十几张表参与连接的较复杂SQL语句。
2、发生性能问题的执行计划:
![bb009ce6be79e97c1282e70ecee837ed.png](https://i-blog.csdnimg.cn/blog_migrate/8225ab4d7f1b5ef1ddb7961e539c8271.jpeg)
--注:
1)计划中节点19在表S_EVT_ACT上发生了FTS,据说该表上有700多W的数据量。
2)计划中节点34在表S_ACT_EMP上发生了index range scan(索引范围扫描)。
3、不存在问题的执行计划:
![67b758538ee93da72eb3f9cd7d1753d0.png](https://i-blog.csdnimg.cn/blog_migrate/d07958c3f79509a15c8073bf5d12033e.jpeg)
--注:
1)计划中节点23在表S_EVT_ACT上走了index unique scan(唯一索引扫描)。
2)计划中节点19在表S_ACT_EMP上走了index range scan(索引范围扫描)。
4、分析:
1)性能问题解决前,计划中节点19在S_EVT_ACT上发生了FTS;节点34在表S_ACT_EMP上发生了index range scan。
2)性能问题解决后,计划中节点23在S_ECT_ACT上发生了index unique scan,且由原来的节点19推后到节点23;节点19在表S_ACT_EMP上发生了index range scan,访问方式与性能问题解决前没发生变化,而为之由原来的节点34被推前到节点19。
3)对比性能问题解决前后,有两个变化,一个是表S_EVT_ACT由原来的FTS变为index unique scan,且为之被推后;另一个是表S_ACT_EMP为之被推前。变化的原因是optimizer_mode有原来的first_rows_10变为all_rows。
4)针对前面讲到的,性能问题解决前后的两个变化,我们讨论下变化的起因和作用。性能问题解决前,因为optimizer_mode设置为first_rows_10,那么,CBO在这个模式下,面对这种比较复杂的多表连接的SQL语句,不会逐个去查询和计算每个参与连接的表的统计信息和成本,而是给出一个粗略评估的结果或默认值,为什么会这样,大家自己思考吧,其实,即使按照这个方法,也未必就一定出现性能问题,因为这种模式下,求的是反应速度,如果表S_EVT_ACT和其他表的连接字段的匹配性足够好,那么,也能达成反应速度最优的效果,这里,问题不在于走了FTS,而是在于表S_EVT_ACT连接字段的匹配性,可这种模式下,CBO不可能得出这个匹配性的准确结果的。因此,这是个非常冒险的决定,可这种模式就是这样,没办法。此外,针对表S_ACT_EM上index range scan在设置为all_rows模式后被推前,这个是无论如何都是应该的,大家看看这个SQL语句就明白了,只是在first_rows_10模式下,CBO发生了错误评估而已,这一点,也许也是影响性能的重要因素之一。
5)由此可见,all_rows_X模式下,尤其是x比较低时,复杂的SQL语句就要小心了,也许,这个模式更适用于oltp业务,而不是olap业务。
个人之见,仅供参考。
注:本文素材来自崔华老师所著SQL优化一书。