Oracle子查詢性能優化一例

　　背景：

　　要做一個birt報表，數據庫是oracle10g，但數據源是從多個數據表中得到的，所以只能寫存儲過程，把數據整合到一個臨時表中去。完成的存儲過程大致如下(只保留主要代碼)：

　　[sql] view plaincopycreateor replace procedure Rpt_Dyhgl_year(

　　rptyear in varchar2, --年yyyy

　　--rpttype in varchar2, --報表類型，1年報，2季報

　　ref_cursor out sys_refcursor --返回游標

　　)

　　As

　　-- 變量聲明

　　。。。。。

　　begin

　　delete from YWTJ_RPT_DYHGL_YEAR where RPTYEAR = rptyear and RPTTYPE = '1';

　　commit;

　　--查詢原始數據

　　v_sql := 'select rptdate, jcdlb, jcdlbName, sum(nvl(runtime,0)) sumruntime,sum(nvl(buhegeTime,0)) buhegeTime, count(jcdcount) jcdcount,

　　sum(nvl(tongbiRuntime,0)) tongbiRuntime, sum(nvl(tongbiBhg,0))tongbiBhg from (

　　select rptdate, runtime,r.chaoshangxian+r.chaoxiaxian buhegeTime, r.objid, 1 jcdcount,

　　(select runtime fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiRuntime,

　　(select chaoshangxian + chaoxiaxian fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiBhg,

　　(select jcdlb from jczl_dwjg d whered.objid = r.objid ) jcdlb,

　　(select jcdlbname from dic_jcdlb wherejcdlbid = (select jcdlb from jczl_dwjg where objid = r.objid)) jcdlbName

　　from ywtj_rpt_dyhgl_month r where rptdate = :rptYear)

　　group by rptdate, jcdlb, jcdlbName

　　order by rptdate ,jcdlb ';

　　-- 插入新數據

　　v_sqlRptInsert := 'Insert IntoYWTJ_RPT_DYHGL_YEAR (RPTYEAR,RPTDATE,RPTTYPE) values(:RPTYEAR,:RPTDATE,:RPTTYPE)';

　　v_sqlRptUpdate := '';

　　-- 計算1--12月的當月數據

　　for rptmonth in 1..12 loop

　　execute immediate v_sqlRptInsertusing rptyear, rptmonth, '1';

　　commit;

　　open v_cur for v_sql using rptyear ||LPAD(rptmonth, 2, '0');

　　loop

　　fetch v_cur intov_rptDate,v_jcdlb,v_jcdName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount,v_tongbiRuntime,v_tongbiBhg;

　　exit when v_cur%notfound;

　　--計算合格率

　　。。。。。。

　　-- 更新當月數據

　　。。。。。。

　　end loop;

　　close v_cur;

　　end loop;

　　-- 更新累計數據

　　。。。。。。

　　commit;

　　-- 計算綜合合格率

　　open ref_cursor for select * fromYWTJ_RPT_DYHGL_YEAR r where r.rptyear = rptyear and r.rpttype = 1 order byr.rptdate;

　　end Rpt_Dyhgl_year;

　　功能運行正常，查詢時間大約30-40秒。

　　然后，把報表部署到運行環境中后，驗證功能時，發現2、3 分鐘報表沒有出來!多次嘗試，后來終于出來結果了：940多秒，15分鐘多!這樣用戶肯定是不能接受的!使用pl/sql dev的性能分析對Rpt_Dyhgl_year的執行過程進行分析，發現幾乎所有的時間都化在了

　　fetch v_cur into v_rptDate,v_jcdlb,v_jcdName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount,v_tongbiRuntime,v_tongbiBhg;

　　(期間把過程轉成程序執行，性能分析時發現，主要時間都是由dr.Read()占用，和對過程的分析一致。)

　　那問題應該是在fetch對應的查詢上，把

　　[sql] view plaincopyv_sql :='select rptdate, jcdlb, jcdlbName,sum(nvl(runtime,0)) sumruntime, sum(nvl(buhegeTime,0)) buhegeTime,count(jcdcount) jcdcount,

　　sum(nvl(tongbiRuntime,0)) tongbiRuntime, sum(nvl(tongbiBhg,0))tongbiBhg from (

　　select rptdate, runtime,r.chaoshangxian+r.chaoxiaxian buhegeTime, r.objid, 1 jcdcount,

　　(select runtime fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiRuntime,

　　(select chaoshangxian + chaoxiaxian fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiBhg,

　　(select jcdlb from jczl_dwjg d whered.objid = r.objid ) jcdlb,

　　(select jcdlbname from dic_jcdlb wherejcdlbid = (select jcdlb from jczl_dwjg where objid = r.objid)) jcdlbName

　　from ywtj_rpt_dyhgl_month r where rptdate = :rptYear)

　　group by rptdate, jcdlb, jcdlbName

　　order by rptdate ,jcdlb '; ①

　　單獨拿出來執行，大約5.7秒多。

　　再把其中的子查詢：

　　[sql] view plaincopyselectrptdate, runtime, r.chaoshangxian+r.chaoxiaxian buhegeTime, r.objid, 1jcdcount,

　　(select runtime fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiRuntime,

原文轉自：http://www.anti-gravitydesign.com