更多动态
展开更多

Oracle数据库优化报告(内蒙古汇能集团数据库性能优化项目)

2020-01-13 22:26 冯工
二维码
78

性能现状

经过一段时间对数据库的性能监控,发现数据库整体负载不高,数据库各性能相关参数也配置合理,CPU,内存,IO等资源也没有存在使用不足的状况,整体性能还是处于一个比较良好的状态。然而AWR报告来看,直接路径读direct path read的等待比较多,在等待事件中位于第三位,虽然总体的百分比不高,但引起这些等待的原因,是由一些关键业务对大表扫描引致;经过分析,这些SQL存在较大的优化空间,下面详细介绍这些SQL的优化过程,并在优化前后分别做了AWR基线与采样,后续运行了性能对比报告,可直观地反映出这次数据库优化的效果。

    


优化方案

数据库性能问题很大程度上是由于应用引起的,而应用反在数据库层面就是SQL代码,所以一个数据库的性能90%以上是由于SQL代码的设计和访问路径不当引起的性能问题,下面是对数据库TOP SQL语句的优化记录,目前已对大部分消耗较高的SQL进行了调整,从后续的数据库优化报告来看,极大减少了IO的消耗,从而提高数据库的性能效率。


优化记录1

Sql_id: 237ky9w81gk6m

    

    

创建索引create index LQ_PUBMESS_PKCORP on PUB_MESSAGEINFO
(PK_CORP,TYPE)
tablespace NNC_DATA01 online;

    

分析:

该语句是数据库并发最大的SQL之一,从EM的性能页面上来观察,也是TOP SQL影响最大的一个SQL,影响率达到30%以上,所以如果能把该语句的IO降下来,数据库的性能将提高一个层次,从优化前的执行计划来看,走的是两表的全表扫描(这里由于时间仓促,没有捕捉下来),COST消耗在2000左右,单次执行虽然在2秒左右,但由于并发过高,所以总的负载消耗很大。


优化措施:

其实该语句主要消耗是在pub_messageinfo的全表扫描上,从该表的数据分布与SQL语句来看,pk_corp与type的条件组合能过滤较大部分数据行,所以可在这两列上创建组合索引,另外,从SQL TUNNING包的优化建议来看,存在一个效果较号的SQL profile,接受后,执行计划产生变化,走了这个组合索引后,COST从2000下降到157,从此该SQL 已移出TOP SQL范围,上图是优化后的效果,单次执行在0.01秒内。


优化记录2

sql_id: cmmx6gpw9ng7q

    

分析:

该语句跟语句1类似,也是由于表pub_messageinfo的全表扫描引致较大消耗,这里有一个比较好的过滤条件是checkman与type,能过滤大部分数据。


优化措施:

在checkman与type上创建组合索引,COST由原来的1000下降到7左右,性能得到较大提升。


优化记录3

    

    

分析:

该SQL原执行计划虽然也是走了索引,但走的是checkman的单列索引,cost仍然有800多,从该表的数据分布与SQL语句分析,ts有较好的选择率,所以可以考虑在checkman与ts上创建组合索引,减少数据块的读取。


优化措施:

在checkman与ts上创建组合索引,执行计划走了新的索引,cost消耗从800多下降到32,减少了IO的顺序读。


优化记录4

    

    

分析:

此语句有较多相似的SQL版本,虽然where条件不同,但优化方法是一样的,该语句原执行计划走了全面扫描,cost超过1万,从该表的数据分布与SQL条件来分析,pk_saleadjust列存在一较好的选择性。


优化措施:

直接在pk_saleadjust列创建索引,执行计划由原来的13235下降到9,极大程度降低了该类SQL语句的IO消耗。


优化记录5

select *

from hgts_lade

where nvl(dr, 0) = 0

  and ((hgts_lade.pk_corp = '1003') and exists

       (select 1

          from hgts_lade_b

         where hgts_lade.pk_lade = hgts_lade_b.pk_lade

           and (hgts_lade_b.pk_cumandoc = '0001H71000000002AG79')

           and (hgts_lade_b.istatus in ('0', '1'))

           and nvl(hgts_lade_b.dr, 0) = 0))

  and (nvl(dr, 0) = 0)

  and pk_corp = '1003'

order by vbillcode asc

    

    

分析:

该语句的性能瓶颈主要是消耗在hgts_lade_b的全表扫描上,COST在3万以上,从该表的数据分布与SQL来看,pk_cumandoc,istatus组合条件能过滤大部分的数据,故可在上面创建组合索引。


优化措施:

在hgts_lade_b表的pk_cumandoc,istatus列上创建组合索引,执行计划走了新的索引,COST消耗已从3万多下降到277,极大程度上减少了IO的消耗。


优化记录6

    

    

分析:

此语句也有较多相似的SQL版本,只是where条件不同,所以优化方法是相似的,该语句原执行计划走了全面扫描,cost超过1万,从该表的数据分布与SQL条件来分析,pk_sinvoice_b,dr组合列存在一较好的选择性。


优化措施:

直接在pk_sinvoice_b,dr列创建组合索引,执行计划由原来的13306下降到143,极大程度降低了该类SQL语句的IO消耗。

create index ix_HGTS_pk_b_dr

on HGTS_MAPPDIV(pk_sinvoice_b,dr) tablespace NNC_INDEX01 online;


其他优化

    

分析:

从上面EM的实时性能图上观察,对大部分 TOP SQL优化后,数据库负载已产生较大下降,从TOP   SQL列表上观看,占据百分比最大的是红色部分的SQL,该SQL其实是stram流的复制语句,目前已占据了接近数据库一半的消耗。


优化建议:

建议撤销stram流的复制机制,该技术已是比较落后的复制技术,而且该软件影响数据库性能较大,目前我们已计划采用goldengate的容灾技术,该软件非常轻量级,对数据库的性能影响较小,所以部署goldengate容灾机制后,stram流复制可撤销掉,从而又可以较大程度提高数据库的性能。



数据库优化效果

在对数据库做性能优化前,我们在做了AWR性能基线报告,也就是能反映数据库优化前的性能状态,然后在优化后运行AWR性能差别报告,下面就是报告的内容,从这个差别报告来看,我们可以比较直接地反映出这次性能优化的效果。

    

报告说明

这两次AWR采样分别是4月13日(优化前),与4月21日(优化后),AWR采样时间间隔都是1小时。


    

报告说明

    上面报告中,1st部分位优化前的报告,2ND部分为优化后的报告,从上面的load profile来看,第一次产生的redo size比第二次少,证明第一次采样时的业务量(繁忙程度)比第二次要多,但第二次的逻辑读与物理读却比第一次分别少了30%与92%,说明优化后的物理读与逻辑读大大减少了,优化效果很显著


    

报告说明

   从上面的等待事件报告来看,第一次采样的直接路径读direct path read等待事件比较多,排行第三位,占了数据库总时间的5%,而第二次采样,直接路径读等待大大减少,已降到0.5%,而且还是业务量增加的情况下,所以说明优化后,直接路径读等待事件已基本上消除,大大减少了物理读等IO类等待

    

报告说明

   从上面的TOP SQL报告来分析,占据前几位的SQL产生的逻辑读已大大减少,而且已从第二次的AWR采样报告中移出TOP SQL范畴,说明了通过TOP SQL的优化,已明显提高了SQL的执行效率,大大减少了数据库的负载,数据库优化效果显著


昵称:
内容:
验证码:
提交评论
评论一下