一个典型的多表参与连接的复杂SQL调优(SQL TUNING)引发的思考

2017-01-04 19:18:03来源:CSDN作者:LHDZ_BJ人点击

第七城市

今天在看崔华老师所著SQL优化一书时,看到他解决SQL性能问题的一个案例,崔华老师成功定位问题并进行了解决。这里,在崔华老师分析定位的基础上,做进一步分析和推理,以便大家一起研究探讨,下面简述该案例场景。

1、发生性能问题的SQL语句:

cu



--注:

  1)十几张表参与连接的较复杂SQL语句。


2、发生性能问题的执行计划:


--注:

  1)计划中节点19在表S_EVT_ACT上发生了FTS,据说该表上有700多W的数据量。

  2)计划中节点34在表S_ACT_EMP上发生了index range scan(索引范围扫描)。

3、不存在问题的执行计划:


--注:

  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优化一书。




第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台