<ruby id="h6500"><table id="h6500"></table></ruby>
    1. <ruby id="h6500"><video id="h6500"></video></ruby>
          1. <progress id="h6500"><u id="h6500"><form id="h6500"></form></u></progress>

            Oracle子查詢(xún)性能優(yōu)化一例

            發(fā)表于:2012-11-07來(lái)源:Csdn作者:littlechang點(diǎn)擊數: 標簽:oracle
            背景: 要做一個(gè)birt報表,數據庫是oracle10g,但數據源是從多個(gè)數據表中得到的,所以只能寫(xiě)存儲過(guò)程,把數據整合到一個(gè)臨時(shí)表中去。完成的存儲過(guò)程大致如下(只保留主要代碼):

              背景:

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

              [sql] view plaincopycreateor replace procedure Rpt_Dyhgl_year(

              rptyear in varchar2, --年yyyy

              --rpttype in varchar2, --報表類(lèi)型,1年報,2季報

              ref_cursor out sys_refcursor --返回游標

              )

              As

              -- 變量聲明

              。。。。。

              begin

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

              commit;

              --查詢(xún)原始數據

              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;

              功能運行正常,查詢(xún)時(shí)間大約30-40秒。

              然后,把報表部署到運行環(huán)境中后,驗證功能時(shí),發(fā)現2、3 分鐘報表沒(méi)有出來(lái)!多次嘗試,后來(lái)終于出來(lái)結果了:940多秒,15分鐘多!這樣用戶(hù)肯定是不能接受的!使用pl/sql dev的性能分析對Rpt_Dyhgl_year的執行過(guò)程進(jìn)行分析,發(fā)現幾乎所有的時(shí)間都化在了

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

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

              那問(wèn)題應該是在fetch對應的查詢(xún)上,把

              [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 '; ①

              單獨拿出來(lái)執行,大約5.7秒多。

              再把其中的子查詢(xún):

              [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://kjueaiud.com

            老湿亚洲永久精品ww47香蕉图片_日韩欧美中文字幕北美法律_国产AV永久无码天堂影院_久久婷婷综合色丁香五月
              <ruby id="h6500"><table id="h6500"></table></ruby>
              1. <ruby id="h6500"><video id="h6500"></video></ruby>
                    1. <progress id="h6500"><u id="h6500"><form id="h6500"></form></u></progress>