一条sql的优化过程

  • darren__chan

    2015-03-24 22:04:14
  • Oracle
  • 原创


点击( 此处)折叠或打开

  1. selectzsxm_dm,
  2.        zspm_dm,
  3.        wspzzl_dm,
  4.        wspzzg,
  5.        wspzhm,
  6.        zsuuid,
  7.        sum(nvl(fpdksk,0))asfpdksk,
  8.        sum(nvl(jsyj,0))asjsyj,
  9.        sl,
  10.        hy_dm,
  11.        skssqq,
  12.        skssqz,
  13.        sksx_dm,
  14.        tfrq,
  15.        djxh,
  16.        yzpzzl_dm
  17.   from(selectjks.djxh,
  18.                yz.yzpzxhaszsuuid,
  19.                yz.yzpzzl_dm,
  20.                jks.pzzl_dmaswspzzl_dm,
  21.                jks.pzzg_dmaswspzzg,
  22.                nvl(jks.pzhm,jks.dzsphm)aswspzhm,
  23.                jks.zsxm_dm,
  24.                jks.zspm_dm,
  25.                jks.sl_1assl,
  26.                jks.jsyj,
  27.                jks.sjjeasfpdksk,
  28.                jks.skssqq,
  29.                jks.skssqz,
  30.                jks.hy_dm,
  31.                jks.kjrqastfrq,
  32.                jks.sksx_dm
  33.           fromhx_zs.zs_jks jks,hx_zs.zs_yjsf yz
  34.          wherejks.tzlx_dmin(\'1\',\'4\')
  35.            andjks.sksx_dmnotlike\'02%\'
  36.            and(jks.kjdjxh=\'10106001062640079\'orjks.djxh=\'10106001062640079\')
  37.            and(1=0orjks.skssqq>=to_date(\'2014-11-01\',\'yyyy-mm-dd\'))
  38.            and(1=0orjks.skssqz<
  39.                to_date(to_char(\'2014-12-31\',\'yyyy-mm-dd\'),\'yyyy-mm-dd\')+1)
  40.            and(1=0orjks.kjrq>=to_date(\'2014-02-01\',\'yyyy-mm-dd\'))
  41.            and(1=0orjks.kjrq<
  42.                to_date(to_char(\'2015-03-31\',\'yyyy-mm-dd\'),\'yyyy-mm-dd\')+1)
  43.            andjks.sjrq_1isnotnull
  44.            andjks.zsuuid=yz.zsuuid
  45.            andyz.tzlx_dmin(\'1\',\'4\')
  46.            andyz.skcllx_dm=\'1\'
  47.            andyz.skzl_dm<>\'20\'
  48.         unionall
  49.         selectwsz.djxh,
  50.                yz.yzpzxhaszsuuid,
  51.                yz.yzpzzl_dm,
  52.                wsz.pzzl_dmaswspzzl_dm,
  53.                wsz.pzzg_dmaswspzzg,
  54.                wsz.pzhmaswspzhm,
  55.                wsz.zsxm_dm,
  56.                wsz.zspm_dm,
  57.                wsz.sl_1assl,
  58.                wsz.jsyj,
  59.                wsz.sjjeasfpdksk,
  60.                wsz.skssqq,
  61.                wsz.skssqz,
  62.                wsz.hy_dm,
  63.                wsz.kjrqastfrq,
  64.                wsz.sksx_dm
  65.           fromhx_zs.zs_wsz wsz,hx_zs.zs_yjsf yz
  66.          wherewsz.tzlx_dmin(\'1\',\'4\')
  67.            andwsz.sksx_dmnotlike\'02%\'
  68.            and(wsz.djxh=\'10106001062640079\'orwsz.kjdjxh=\'10106001062640079\')
  69.            and(1=0orwsz.skssqq>=to_date(\'2014-11-01\',\'yyyy-mm-dd\'))
  70.            and(1=0orwsz.skssqz<
  71.                to_date(to_char(\'2014-12-31\',\'yyyy-mm-dd\'),\'yyyy-mm-dd\')+1)
  72.            and(1=0orwsz.kjrq>=to_date(\'2014-02-01\',\'yyyy-mm-dd\'))
  73.            and(1=0orwsz.kjrq<
  74.                to_date(to_char(\'2015-03-31\',\'yyyy-mm-dd\'),\'yyyy-mm-dd\')+1)
  75.            andwsz.zsuuid=yz.zsuuid
  76.            andyz.tzlx_dmin(\'1\',\'4\')
  77.            andyz.skcllx_dm=\'1\'
  78.            andyz.skzl_dm<>\'20\')b
  79.  groupbyzsxm_dm,
  80.           zspm_dm,
  81.           wspzzl_dm,
  82.           wspzzg,
  83.           wspzhm,
  84.           zsuuid,
  85.           sl,
  86.           hy_dm,
  87.           skssqq,
  88.           skssqz,
  89.           sksx_dm,
  90.           tfrq,
  91.           djxh,
  92.           yzpzzl_dm;



初步分析：
1.前天数据做过收集统计，而收集统计之前未发现该sql超时问题。
2.此sql本身耗费确实较高，需要进一步优化。
3.由执行计划初步可看耗费较高的地方是在hx_zs.zs_wsz上走索引IDX_ZS_WSZS_SKFJ_KJDJXH_SSQQ时采取了INDEX SKIP SCAN ,COST达到了2064。
4.HX_ZS.ZS_JKS及HX_ZS.ZS_WSZ这两张表是分区表，而SQL中却未添加分区关键条件，造成的对所有分区的扫描。
5.另外，以上sql的结构模式是 select  (jks, yjsf  union all wsz,yjsf) where gruop by ，两次对同一个表进行扫描。


优化步骤：
1.收集统计。针对sql中涉及的表做了收集统计！
2. jks,wsz加上skssswjg条件，因为这两个表是以skssswjg进行分区。
3. sql结构调整为select (jks union all wsz),yjsf where gruop by 结构！
4. 试着加上kjdjxh和skssqq的两列索引看看避免skip scan后的效果

      执行以上步骤之后sql的执行计划 cost降到了7，cpu耗费增到1千7百万左右，时间为1
附件是修改后sql等！


修改后sql:

点击( 此处)折叠或打开

  1. select b .zsxm_dm ,
  2.        b .zspm_dm ,
  3.        wspzzl_dm ,
  4.        wspzzg ,
  5.        wspzhm ,
  6.        b .zsuuid ,
  7.         sum ( nvl (b .fpdksk , 0 ) ) as fpdksk ,
  8.         sum ( nvl (b .jsyj , 0 ) ) as jsyj ,
  9.        sl ,
  10.        b .hy_dm ,
  11.        b .skssqq ,
  12.        b .skssqz ,
  13.        b .sksx_dm ,
  14.        tfrq ,
  15.        b .djxh ,
  16.        yz .yzpzzl_dm
  17.    from ( ( select jks .djxh ,
  18.                jks .pzzl_dm as wspzzl_dm ,
  19.                jks .pzzg_dm as wspzzg ,
  20.                 nvl (jks .pzhm , jks .dzsphm ) as wspzhm ,
  21.                jks .zsxm_dm ,
  22.                jks .zspm_dm ,
  23.                jks .sl_1 as sl ,
  24.                jks .jsyj ,
  25.                jks .sjje as fpdksk ,
  26.                jks .skssqq ,
  27.                jks .skssqz ,
  28.                jks .hy_dm ,
  29.                jks .kjrq as tfrq ,
  30.                jks .sksx_dm ,
  31.                jks .zsuuid
  32.            from hx_zs .zs_jks jks
  33.           where jks .tzlx_dm in ( \ '1\' , \ '4\' )
  34.             and jks .sksx_dm not like \ '02%\'
  35.             and jks .skssswjg_dm = \ '24401030000\'
  36.             and (jks .kjdjxh = \ '1016001062640079\' or jks .djxh = \ '1016001062640079\' )
  37.             and (1 = 0 or jks .skssqq > = to_date ( \ '2014-11-01\' , \ 'yyyy-mm-dd\' ) )
  38.             and (1 = 0 or jks .skssqz <
  39.                 to_date ( to_char ( \ '2014-12-31\' , \ 'yyyy-mm-dd\' ) , \ 'yyyy-mm-dd\' ) + 1 )
  40.             and (1 = 0 or jks .kjrq > = to_date ( \ '2014-02-01\' , \ 'yyyy-mm-dd\' ) )
  41.             and (1 = 0 or jks .kjrq <
  42.                 to_date ( to_char ( \ '2015-03-31\' , \ 'yyyy-mm-dd\' ) , \ 'yyyy-mm-dd\' ) + 1 )
  43.             and jks .sjrq_1 is not null
  44.          union all
  45.          select wsz .djxh ,
  46.                wsz .pzzl_dm as wspzzl_dm ,
  47.                wsz .pzzg_dm as wspzzg ,
  48.                wsz .pzhm as wspzhm ,
  49.                wsz .zsxm_dm ,
  50.                wsz .zspm_dm ,
  51.                wsz .sl_1 as sl ,
  52.                wsz .jsyj ,
  53.                wsz .sjje as fpdksk ,
  54.                wsz .skssqq ,
  55.                wsz .skssqz ,
  56.                wsz .hy_dm ,
  57.                wsz .kjrq as tfrq ,
  58.                wsz .sksx_dm ,
  59.                wsz .zsuuid
  60.            from hx_zs .zs_wsz wsz
  61.           where wsz .tzlx_dm in ( \ '1\' , \ '4\' )
  62.             and wsz .sksx_dm not like \ '02%\'
  63.             and wsz .skssswjg_dm = \ '24401030000\'
  64.             and (wsz .djxh = \ '1016001062640079\' or wsz .kjdjxh = \ '1016001062640079\' )
  65.             and (1 = 0 or wsz .skssqq > = to_date ( \ '2014-11-01\' , \ 'yyyy-mm-dd\' ) )
  66.             and (1 = 0 or wsz .skssqz <
  67.                 to_date ( to_char ( \ '2014-12-31\' , \ 'yyyy-mm-dd\' ) , \ 'yyyy-mm-dd\' ) + 1 )
  68.             and (1 = 0 or wsz .kjrq > = to_date ( \ '2014-02-01\' , \ 'yyyy-mm-dd\' ) )
  69.             and (1 = 0 or wsz .kjrq <
  70.                 to_date ( to_char ( \ '2015-03-31\' , \ 'yyyy-mm-dd\' ) , \ 'yyyy-mm-dd\' ) + 1 ) ) ) b , hx_zs .zs_yjsf yz
  71.            where b .zsuuid = yz .zsuuid
  72.             and yz .tzlx_dm in ( \ '1\' , \ '4\' )
  73.             and yz .skcllx_dm = \ '1\'
  74.             and yz .skzl_dm < > \ '20\'
  75.   group by b .zsxm_dm ,
  76.           b .zspm_dm ,
  77.           wspzzl_dm ,
  78.           wspzzg ,
  79.           wspzhm ,
  80.           b .zsuuid ,
  81.           sl ,
  82.           b .hy_dm ,
  83.           b .skssqq ,
  84.           b .skssqz ,
  85.           b .sksx_dm ,
  86.           tfrq ,
  87.           b .djxh ,
  88.           yzpzzl_dm ;




请使用浏览器的分享功能分享到微信等