单条SQL3000倍的提升,解决数据库如芒在背的顽疾

电信某系统应用人员反馈程序整体运行非常慢,部分业务甚至无法完成,还给出了好几个语句报ora-01555错误的截图,涉及到集团扣分,我必须尽快解决。

整体分析AWR是必不可少的,从统计上看存储每秒读取量110m左右,读取块数14250左右,相对于主流存储的处理能力来说压力并不是很大,但是数据库主要I/O等待事件的平均延迟高达22ms之多,整体拖慢了SQL运行速度,从数据库角度看存储表现出的综合性能偏低,由于数据库是在集团的虚机上暂不清楚是本身性能有限还是和其它项目有争用导致。但从业务角度看这个物理读偏高,物理读和逻辑读的比率才1/10左右,和业务规模不太匹配。

除了慢以外还有部分语句执行时报ORA-1555错误中断,根本无法完成。当前报错的语句都是运行了一段时间报错的,根本上其实还是运行的慢,拖的时间过长以至于找不到查询刚开始时的数据了。

错误说明:

ORA-01555错误是一种在Oracle数据库中很常见的错误。在数据库越来越智能的今天虽然越来越少但仍然不可避免。而出现ORA-01555错误,通常情况是SQL语句执行时间太长,或者UNDO表空间过小,或者事务量过大,或者过于频繁的提交,导致执行SQL过程中进行一致性读时,SQL执行后修改的前镜像(即UNDO数据)在UNDO表空间中已经被覆盖,不能构造一致性读块。

解决方法不外乎开源节流两种,更换高性能尤其是高IOPS的存储或者优化程序降低系统I/O使用,最快解决当前问题靠前者无疑是不现实的。

数据库优化根本上就是降低逻辑读和物理读,我采用的最原始的方法进行优化,取多个时段的AWR报告,对AWR报告中的所有上榜语句挨个分析。

首先映入眼帘的是一个存储过程,逻辑读高达10位数,排行榜第一实至名归。虽然优化存储过程的成本可能很高,但一旦有所斩获收益也是巨大的,就从它开始了。从上到下快速浏览一遍存储过程内容后长出了一口气,应该不会出现一个SQL拼接完成后千上万行的情况。该过程所有核心内容都是在一个循环中完成的,外层循环语句如下:

这个表是个配置表,有22条记录,数据是22张表的表名、

用户名等信息。

循环里面有几个独立begin end块,每个块都有Exception,Exception中都是记录日志,看了一下运行记录没异常,逻辑读都是正常运行中产生的,简单的变量拼接判断后出现了第一个语句:

以这条语句查询的记录做loop,循环里面有一个update语句:

看到这不得不产生一个疑问,这个标记列到底是varchar还是number?

每个表都DESC了一下,确认都是VARCHAR2(2)。看来突破点很可能就应在这儿了,查了一下表,

标记列为0的值很少,甚至没有,这和上面的处理逻辑相符,这个列数据倾斜严重,几乎全是1,所以对这个语句来说修改成字符型也只能指望batch_id列适合建索引了。

乍一看区分度不高不容乐观,但是根据业务逻辑判断这个列数据倾斜很可能也非常严重,实际需要的只是很少的那部分。

正常情况下可以把存储过程中的语句提出来模拟运行推演出来所需变量值,绑定变量的可以通过在v$sql中like语句拿到SQL_ID再通过V$SQL_BIND_CAPTURE查询出变量,这次我偷了点懒,直接找到反馈问题的应用开发人员拿到了变量值,他告诉我这个值一段时间不会变而且正如之前推测的那样实际记录是很少的。

查询语句可以利用一个索引,修改语句可以利用两个索引,再往下看存储过程中还有类似

这样的语句,也就是说整体上看在这两个列上分别建索引更合适。虽然数据库开启了ACS但此处用不上,不用担心有什么副作用,查了几个表还没有建索引,收益可期,幸福来的之早已经超出期望了,想想都能笑出声。

过程下面一块核心部分就是一个update语句:

这个语句本质上和上面的一样,可以从batch_id列索引受益,根据以往经验这种情况如果把这一条SQL实现的UPDATE拆开用BLUK COLLECT和FORALL组合的PLSQL实现效率可能更上一层楼,但是涉及两个问题,一是这种代码修改幅度较大改动需要做好充分的准备确认收益足够大才能说服应用去改,与当前最短时间获得最多优化提升的目标不符。二是dblink那边的数据库不能直接管,不方便测试出了纰漏也不好处理。为了避免节外生枝战略性放弃。

最后一段内容如下:

把1的改成2,和前面对照明显是收尾了,同样受益于batch_id列索引。

整个过程分析完毕接下来我和开发人员做的就是没有建这两个索引的表建上索引以及修正存储过程中所有类型不正确的错误。

效果对比:

调整前逻辑读195686,调整后1055,效果明显。

幸运的是后续也没有遇到非常复杂的过程,在应用的配合下通过建索引、重建表降低高水位线等基本方法对10个表以及存储过程做了优化调整,整过过程非常顺利用时2天。优化后应用侧反馈程序运行效率提高2-3倍不等,而且比较稳定,没有出现之前有的语句慢到什么无法接受的情况,程序执行报错的问题也没出现过。业务顺利运行。

优化前后1小时整体指标对比:

通过优化前后1小时AWR对比发现逻辑读和物理读都有大幅度下降,在Redo size增长185%情况下,逻辑读物理读分别下降57%42%I/O等待延迟从22ms下降到8ms, 降到8ms基本没有太明显的影响了,可以接受。


优化前后12小时整体指标对比:

长达12小时的统计能更准确体现数据库整体运行情况,通过对比可以看到优化后几乎所有指标都大幅度下降,其中I/O类等待下降60%-78%不等




优化后依然上榜的语句效率都有不同程序提升,个别语句性能提升3000多倍,如sql:58p5f8tts41gh,也就是最开始优化的存储过程,优化前单次执行37101秒,优化后仅需11秒,执行时间下降99.97%。如sql:58p5f8tts41gh,优化前执行一次20645秒,优化后已经榜上无名了。

未经允许不得转载:Oracle一体机用户组 » 单条SQL3000倍的提升,解决数据库如芒在背的顽疾

相关推荐