性能现状

经过一段时间对数据库的性能监控，发现数据库整体负载不高，数据库各性能相关参数也配置合理，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部分位优化前的报告，2^ND部分为优化后的报告，从上面的load profile来看，第一次产生的redo size比第二次少，证明第一次采样时的业务量（繁忙程度）比第二次要多，但第二次的逻辑读与物理读却比第一次分别少了30%与92%，说明优化后的物理读与逻辑读大大减少了，优化效果很显著。

报告说明：

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

报告说明：

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