一条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/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/29863023/viewspace-1472438/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值