很多系统上线后, 性能问题开发就基本上不管了 , 业务越来越慢的责任都压在DBA身上,而大部分DBA对SQL优化没有深入的研究, 就只能把希望寄托在硬件的改善上.
最近遇到很多业务越来越慢的案例,都是SQL写法导致的问题,实现相同的业务逻辑, SQL写法不同, 性能相差几倍到几千倍,消耗的硬件资源也相差甚远.
比如下面这个SQL,这种写法就是极其糟糕的, 可就是这种低效标量子查询的写法,很多开发人员都愿意使用:
这个sql的改写比较简单,有兴趣的可以自己练练手. 数据量小的时候差别不大, 数据量大了, 差别越来越大, 直到慢慢耗光你的硬件资源.
最近帮某个银行客户分析了两套oracle数据库, 客户反映说是系统慢, 迁移到了新的硬件平台,还是慢. 收集了AWR,看了几个top SQL,都是写法欠佳,下面是其中之一.
获取的sql monitor执行计划如下, 执行时间一小时以上,其中一个大分区表(610个分区)的全表扫描消耗占了绝大部分:
SQL代码如下:
问题的关键在于最后一个红框的写法,EP2EAS_ITGOPENACCOUNT_HIST表是以LOAD_DATE字段做按天list分区, 因为在分区字段上使用了函数,优化器无法做分区裁剪,只能扫描全部分区.
如果我们把函数放到前面FST.TRANSDATE字段上,那样就不需要读610个分区,只需要读1个分区就行了,即把FST.TRANDATE = TO_CHAR (TO_DATE (SCD.LOAD_DATE, 'yyyymmdd'), 'yyyy-mm-dd') 改成to_char(TO_DATE (FST.TRANDATE, 'yyyy-mm-dd'),'yyyymmdd')=SCD.LOAD_DATE .
从610个分区到1个分区, 效率提升应该比较清楚了.
可能有人会问, FST.TRANDATE字段上使用了函数, 不会对其他的分区表有影响吗? 不会的, 因为FST.TranDate来自LG表,而且LG表的分区字段是LOAD_DATE, 即使是tranDate是分区字段,因为前面已经有了LG.TranDate=to_char(),也可以在这一步就完全分区裁剪, 不影响后面的关联. 如果我们在SCD子查询内部的STATUS='0'后面也增加一个and LOAD_DATE= :B1 , 结果集等价,效果也是一样的, 就不用做前面那个改动了.
这个SQL如果不改, 分区数再逐渐增加, 执行效率还会逐渐变差, 而改写之后的SQL, SQL的执行效率基本上就不会再有大的变化了,除非每天的数据量有很大改变.
这种SQL写法导致的性能问题, 靠补强硬件是没有意义的, 而且一开始系统上线的时候影响还不太明显(分区数少), 随着时间的推移, 分区数越来越大, 效率就越来越差.
大部分人对优化的想法还停留在调整几个DB或是OS参数就能搞定的阶段,其实不然.因为DBA大部分都是持证上岗, 硬件配置和参数基本上都没有什么大的问题. 性能优化, 从开发入手, 做SQL优化, 带来的收益才是最大的.
