記錄通過執行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低也不代表計劃好；它只是一種指導優化器的依據。

總結

以上是生活随笔為你收集整理的oracle执行脚本顺序执行吗,【ORACLE】记录通过执行Oracle的执行计划查询SQL脚本中的效率问题 - 不及格的飞鱼...的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。