记录通过执行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低也不代表计划好；它只是一种指导优化器的依据。