一條sql的優化過程

darren__chan發表於2015-03-24
SQL優化

點選(此處)摺疊或開啟

  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;



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29863023/viewspace-1472438/，如需轉載，請註明出處，否則將追究法律責任。

相關文章