一个查询非常慢的sql优化

今天接到投诉，一个页面打不开，经过和应用代维的同事沟通后，发现是一条语句的问题，这条语句执行起来非常慢，大概要3分钟才能刷出来，语句如下

select thisTableAlias.wid,resultset.BJMC from V_ZXJ_YSDJTJ thisTableAlias left join (select BJDM,BJMC from T_XSJBXX_BJ) resultset on thisTableAlias.BJDM=resultset.BJDM where thisTableAlias.wid in (,,,,,,);

其中V_ZXJ_YSDJTJ这个对象是一个视图，此视图包含了非常复杂的查询；

create or replace view usr_xg.v_zxj_ysdjtj as
select wid,zxjzlbm,zxjdjbm,xn,dsrs,dsje,ssrs,ssje,hzrs,hzje,thrs,slxd,jexd,yxsh,bjdm,xydm from(
select  dj.zxjzlbm || dj.zxjdjbm || unit.yxsh||unit.bjdm||zl.pdxn as wid,
       dj.zxjzlbm,
       dj.zxjdjbm,
       dj.slxdqbm,
      dj.jexdqbm,
       zl.pdxn as xn,
       unit.bjdm,
        unit.yxsh,
        unit.xydm,
        (Select Count(1)
          From T_ZXJ_XX xx
         Where xx.zxjzlbm = dj.zxjzlbm
           and xx.zxjdjbm = dj.zxjdjbm
           And xx.xn = zl.pdxn
           And xh In (Select xh
                        From t_xsjbxx_xsjbb jbb
                       Where jbb.bjdm = unit.bjdm
                         and jbb.yxsh = unit.yxsh)
           and xx.shzt = 2) As dsRS,
       (Select sum(je)
          From T_ZXJ_XX xx
         Where xx.zxjzlbm = dj.zxjzlbm
           and xx.zxjdjbm = dj.zxjdjbm
           And xx.xn = zl.pdxn
           And xh In (Select xh
                        From t_xsjbxx_xsjbb jbb
                       Where jbb.bjdm = unit.bjdm
                         and jbb.yxsh = unit.yxsh)
           and xx.shzt = 2) As dsJE,
       (Select Count(1)
          From T_ZXJ_XX xx
         Where xx.zxjzlbm = dj.zxjzlbm
           and xx.zxjdjbm = dj.zxjdjbm
           And xx.xn = zl.pdxn
           And xh In (Select xh
                        From t_xsjbxx_xsjbb jbb
                       Where jbb.bjdm = unit.bjdm
                         and jbb.yxsh = unit.yxsh)
           and xx.shzt = 3) As ssRS,
       (Select sum(je)
          From T_ZXJ_XX xx
         Where xx.zxjzlbm = dj.zxjzlbm
           and xx.zxjdjbm = dj.zxjdjbm
           And xx.xn = zl.pdxn
           And xh In (Select xh
                        From t_xsjbxx_xsjbb jbb
                       Where jbb.bjdm = unit.bjdm
                         and jbb.yxsh = unit.yxsh)
           and xx.shzt = 3) As ssJE,
       (Select Count(1)
          From T_ZXJ_XX xx
         Where xx.zxjzlbm = dj.zxjzlbm
           and xx.zxjdjbm = dj.zxjdjbm
           And xx.xn = zl.pdxn
           And xh In (Select xh
                        From t_xsjbxx_xsjbb jbb
                       Where jbb.bjdm = unit.bjdm
                         and jbb.yxsh = unit.yxsh)
           and xx.shzt = 99) As hzRS,
       (Select Count(1)
          From T_ZXJ_XX xx
         Where xx.zxjzlbm = dj.zxjzlbm
           and xx.zxjdjbm = dj.zxjdjbm
           And xx.xn = zl.pdxn
           And xh In (Select xh
                        From t_xsjbxx_xsjbb jbb
                       Where jbb.bjdm = unit.bjdm
                         and jbb.yxsh = unit.yxsh)
           and xx.shzt < 0) As thRS,
       (Select sum(je)
          From T_ZXJ_XX xx
         Where xx.zxjzlbm = dj.zxjzlbm
           and xx.zxjdjbm = dj.zxjdjbm
           And xx.xn = zl.pdxn
           And xh In (Select xh
                        From t_xsjbxx_xsjbb jbb
                       Where jbb.bjdm = unit.bjdm
                         and jbb.yxsh = unit.yxsh)
           and xx.shzt = 99) As hzJE,
       (select slxd
          from t_pub_e_detailnumlim sl
         where sl.slxdqbm = dj.slxdqbm
           and unit.bjdm = sl.jd) as SLXD,
       (select jexd
          from t_pub_e_detailmoneylim jl
         where jl.jexdqbm = dj.jexdqbm
           and unit.bjdm = jl.jd) as JEXD
  from usr_xg.t_zxj_dj dj
  left join usr_xg.t_zxj_zl zl on dj.zxjzlbm = zl.zxjzlbm
 cross join (Select  a.bjdm, a.yxsh,(select xydm from usr_xg.v_zxbz_dw where dwdm=a.yxsh) xydm From usr_xg.t_xsjbxx_xsjbb a   group by (a.bjdm, a.yxsh)) unit
 where dj.sfyx = 1
 group by (dj.zxjzlbm, dj.zxjdjbm, dj.slxdqbm, dj.jexdqbm, zl.pdxn,
           unit.bjdm, unit.yxsh,unit.xydm));

单查此视图倒是很快，可以马上刷出来，但用left join和别的表关联查询，就需要把所以数据查出来才能返回，所以会很慢，可以用create table xxx as  usr_xg.v_zxj_ysdjtj 来查看执行速度，经过测试，用时1分30秒，用建的表左联接，速度为零点几秒，所以想到了用物化视图来解决此问题，建立如下物化视图：

create materialized view mv_zxj_ysdjtj
 refresh force on demand start with sysdate next sysdate+4/1440  as select * from  v_zxj_ysdjtj;

这样前台页面就能很快刷新出页面了，但这并没有解决关键问题，只是把问题转入幕后，物化视图刷新一次要1分30秒，还是很耗费cpu及内存的，从awr报告中也可以看到，cpu time非常高，此视图还有优化的空间，经过分析，把视图中重复对一张表查询而count或sum的表修改为左联接，然后用case when的方式count或sum，就减少了对表的扫描，修改为如下代码：

create table tmp_mcl_20121017 as
select wid,zxjzlbm,zxjdjbm,xn,dsrs,dsje,ssrs,ssje,hzrs,hzje,thrs,slxd,jexd,yxsh,bjdm,xydm from(
select  dj.zxjzlbm || dj.zxjdjbm || unit.yxsh||unit.bjdm||zl.pdxn as wid,
       dj.zxjzlbm,
       dj.zxjdjbm,
       dj.slxdqbm,
      dj.jexdqbm,
       zl.pdxn as xn,
       unit.bjdm,
        unit.yxsh,
        unit.xydm,
        sum(decode(shzt,2,1,0)) dsrs,
        sum(decode(shzt,2,xx.je,0)) dsJE,
        sum(decode(shzt,3,1,0)) ssrs,
        sum(decode(shzt,3,xx.je,0)) ssJE,
        sum(decode(shzt,99,1,0)) hzrs,
        sum(decode(shzt,99,xx.je,0)) hzJE,
        sum(case when shzt<0 then 1 else 0 end) thrs,
       (select slxd
          from t_pub_e_detailnumlim sl
         where sl.slxdqbm = dj.slxdqbm
           and unit.bjdm = sl.jd) as SLXD,
       (select jexd
          from t_pub_e_detailmoneylim jl
         where jl.jexdqbm = dj.jexdqbm
           and unit.bjdm = jl.jd) as JEXD
  from t_zxj_dj dj
  left join t_zxj_zl zl on dj.zxjzlbm = zl.zxjzlbm
 cross join (Select  a.bjdm, a.yxsh,(select xydm from v_zxbz_dw where dwdm=a.yxsh) xydm From t_xsjbxx_xsjbb a   group by (a.bjdm, a.yxsh)) unit
 left join t_xsjbxx_xsjbb jbb on  jbb.bjdm = unit.bjdm
                         and jbb.yxsh = unit.yxsh
  left join T_ZXJ_XX xx on xx.zxjzlbm = dj.zxjzlbm
           and xx.zxjdjbm = dj.zxjdjbm
           And xx.xn = zl.pdxn and xx.xh =jbb.xh
 where dj.sfyx = 1 and jbb.xh is not null
 group by (dj.zxjzlbm, dj.zxjdjbm, dj.slxdqbm, dj.jexdqbm, zl.pdxn,
           unit.bjdm, unit.yxsh,unit.xydm));

这样优化后物化视图刷新只需9秒，速度快了很多；

 

总结：前台直接关联查询很多表的情况下，sql执行时要关联操作，非常耗资源，如果能把这些耗资源的一部分做成物化视图，物化视图就相当于一个表，可以在此物化视图基础上再查询，减少了很多关联，而且数据可以cache到sga中，增加了可重用的概率，语句执行效果会很好多，但这并不是优化的终点，明天再想想办法，能不能用写个存储过程，过程中用临时表，直接用java代码调用过程，这样数据实时性就会提高很多。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26805071/viewspace-746650/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26805071/viewspace-746650/