Oracle子查询性能优化一例

最新推荐文章于 2024-05-30 15:53:04 发布

littlechang

最新推荐文章于 2024-05-30 15:53:04 发布

阅读量7.2k

点赞数

分类专栏：数据库文章标签： oracle date 报表 oracle10g sql insert

本文链接：https://blog.csdn.net/littlechang/article/details/7593703

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

背景：

要做一个birt报表，数据库是oracle10g，但数据源是从多个数据表中得到的，所以只能写存储过程，把数据整合到一个临时表中去。完成的存储过程大致如下（只保留主要代码）：

createor 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对应的查询上，把

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

单独拿出来执行，大约5.7秒多。

再把其中的子查询：

selectrptdate, 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,

       (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;   ②

单独拿出来执行，速度很快，但返回的数据量很大。如果再对其循环分析，估计性能也好不到哪去，先行放弃这种尝试。

把查询②中的tongbiRuntime和tongbiBhg两个字段子查询去掉，执行速度更快，同样再把①中的tongbiRuntime和tongbiBhg两个字段子查询去掉，执行速度提高很多，时间约0.157秒。

这样速度就提高了很多，但少了去年同比的数据，那就分成两个查询！按这个思路下来，最后得到的过程如下（只保留主要部分）：

createor replace procedure Rpt_Dyhgl_year(
--电压合格率-年
  v_rptyear            in varchar2,  --年yyyy
  --rpttype            in varchar2,   --报表类型，1年报，2季报
  ref_cursor           out sys_refcursor --返回游标
)
as
 -- 变量声明
 .......
begin
   delete from YWTJ_RPT_DYHGL_YEAR where  RPTYEAR = v_rptyear and  RPTTYPE = '1';
   commit;
  --查询原始数据
  v_sql := 'select rptdate, jcdlb,  jcdlbName, sum(nvl(runtime,0)) sumruntime,sum(nvl(buhegeTime,0)) buhegeTime, count(jcdcount) jcdcount
        from ( select rptdate, runtime,r.chaoshangxian+r.chaoxiaxian buhegeTime, r.objid, 1 jcdcount,
       (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
     -- 打开游标开始计算合格率
     open v_cur for v_sql using v_rptyear||LPAD(rptmonth, 2, '0');
     loop
         fetch v_cur intov_rptDate,v_jcdlb,v_jcdLbName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount;
         exit when v_cur%notfound;
         --计算合格率
         if v_sumRuntime <> 0 then
            v_hgl := (v_sumRuntime-v_sumbuhegeTime)/v_sumRuntime *100;
         end if;
         -- 更新当月数据
         ........
       end loop;
       close v_cur;
     -- 打开游标开始计算同比合格率
     open v_cur for v_sql using (v_rptyear-1)|| LPAD(rptmonth, 2, '0');
     loop
         fetch v_cur into v_rptDate,v_jcdlb,v_jcdLbName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount;
         exit when v_cur%notfound;
         --计算合格率
         if v_sumRuntime <> 0 then
            v_hgl :=  (v_sumRuntime-v_sumbuhegeTime)/v_sumRuntime*100;
         end if;
         -- 更新同比数据
         .........
       end loop;
       close v_cur;
   end loop;

   -- 更新累计数据
    ......... 
   -- 计算综合合格率
   ........

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

经验证，执行速度大约在一秒多，也就是说性能从原来的900多秒提高到1秒，提高差不多三个数量级。

分析：

1）在数据量大时，子查询的性能会受到比较大的影响；

2）在子查询中使用了函数to_char(add_months(to_date(tb.rptdate,'yyyymm'),12),'yyyymm')，对大数据量查询时，执行函数也会对系统产生比较大的影响。

littlechang

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Oracle子查询性能优化一例

背景：要做一个birt报表，数据库是oracle10g，但数据源是从多个数据表中得到的，所以只能写存储过程，把数据整合到一个临时表中去。完成的存储过程大致如下（只保留主要代码）：createor replace procedure Rpt_Dyhgl_year( rptyear in varchar2, --年yyyy --rpttype
复制链接

扫一扫

专栏目录