【ORACLE】记录通过执行Oracle的执行计划查询SQL脚本中的效...

efdf

记录通过执行Oracle的执行计划查询SQL脚本中的效率问题

问题现象： STARiBOSS5.8.1R2版本中，河北对帐JOB执行时，无法生成发票对帐文件。

首先，Quartz表达式培植的启动时间为2分钟执行一次JOB，通过日志上看，该JOB已经启动。且在开始统计发票信息后就没有新的日志了，因河北广电数据库很大，猜测是统计发票的SQL 效率低。在比较小的库上执行，系统正常生成发票对帐文件，因此确认猜测为正确的。

然后，将发票统计的SQL截取出来，其中一个SQL如下：

---

select n.noteid_pk,
       n.notecodestr,
       n.totalmoneyid,
       n.notestatusid,
       n.createdt,
       n.modifydt,
       n.taxregisrationno,
       n.securitycode,
       pi.printinstanceid_pk,
       o.citycode
  from noteen n, printinstanceen pi, salechannelen s, operareaen o
where n.salechannelid1 = s.salechannelid_pk
   and s.operareaid_pk = o.operareaid_pk
   and n.noteid_pk = pi.printpaperid(+)
   and n.taxregisrationno is not null
   AND o.citycode = '0667'
   and n.notetypeid in
       (select r.resourcecataid_pk
          from resourcecataen r
         where 1 = 1
           AND (r.resourcecatanamestr = '河北新发票'))
   and trunc(n.createdt) = to_date('2013-05-22', 'yyyy-MM-dd');

---

在 河北广电数据库中执行该脚本运行时间为170S以上，因此需要优化统计的SQL。优化前，首先需要找到SQL的查询效率低的“瓶颈”，然后再优化SQL

使用PL/SQL 从Tools - Explain Plan中可以查看该SQL的执行计划，找出SQL的“瓶颈”在那儿。如图所示

---

这段SQL的执行计划如下图： 其中Cost 总花费为182682 ,查询PRINTINSTANCEEN 时，Cost花费为180913，且查询时候使用了FULL全表扫描
因此可见，查询品“瓶颈”为关联表PRINTINSTANCEEN ；
SQL中关联时使用了 “  and n.noteid_pk = pi.printpaperid(+)” 因此，查询PRINTINSTANCEEN 表信息，发现PRINTINSTANCEEN 数据量很大，大概有800W数据。
关联查询PRINTINSTANCEEN 表时，进行了全表扫描，表数据又很大，因此效率低，为提高效率，为printpaperid添加索引。

---

再次从Tools - Explain Plan中可以查看该SQL的执行计划。发现查循关联PRINTINSTANCEEN 时已经不是FULL全表扫描，
而是使用了INDEXRANGESCAN(索引IDX$PRINTPAPERID) 查询COST 已经下降到7， 从数据库中运行脚本查询速度为0.025。



备注：
cost只是指导值，Oracle优化器通过对象的统计信息来计算相关计划的成本cost，并通过cost的高低来衡量有限的几种可用计划。但cost高并不代表计划就不好，cost低也不代表计划好；它只是一种指导优化器的依据。