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

时间:2022-12-11 07:49:41
记录通过执行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的“瓶颈”在那儿。如图所示 
【ORACLE】记录通过执行Oracle的执行计划查询SQL脚本中的效率问题
 

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

再次从Tools - Explain Plan中可以查看该SQL的执行计划。发现查循关联PRINTINSTANCEEN 时已经不是FULL全表扫描,
而是使用了INDEXRANGESCAN(索引IDX$PRINTPAPERID) 查询COST 已经下降到7, 从数据库中运行脚本查询速度为0.025。
 
 【ORACLE】记录通过执行Oracle的执行计划查询SQL脚本中的效率问题
 
备注:
cost只是指导值,Oracle优化器通过对象的统计信息来计算相关计划的成本cost,并通过cost的高低来衡量有限的几种可用计划。
但cost高并不代表计划就不好,cost低也不代表计划好;它只是一种指导优化器的依据。