点击(此处)折叠或打开
- selectzsxm_dm,
- zspm_dm,
- wspzzl_dm,
- wspzzg,
- wspzhm,
- zsuuid,
- sum(nvl(fpdksk,0))asfpdksk,
- sum(nvl(jsyj,0))asjsyj,
- sl,
- hy_dm,
- skssqq,
- skssqz,
- sksx_dm,
- tfrq,
- djxh,
- yzpzzl_dm
- from(selectjks.djxh,
- yz.yzpzxhaszsuuid,
- yz.yzpzzl_dm,
- jks.pzzl_dmaswspzzl_dm,
- jks.pzzg_dmaswspzzg,
- nvl(jks.pzhm,jks.dzsphm)aswspzhm,
- jks.zsxm_dm,
- jks.zspm_dm,
- jks.sl_1assl,
- jks.jsyj,
- jks.sjjeasfpdksk,
- jks.skssqq,
- jks.skssqz,
- jks.hy_dm,
- jks.kjrqastfrq,
- jks.sksx_dm
- fromhx_zs.zs_jks jks,hx_zs.zs_yjsf yz
- wherejks.tzlx_dmin(\'1\',\'4\')
- andjks.sksx_dmnotlike\'02%\'
- and(jks.kjdjxh=\'10106001062640079\'orjks.djxh=\'10106001062640079\')
- and(1=0orjks.skssqq>=to_date(\'2014-11-01\',\'yyyy-mm-dd\'))
- and(1=0orjks.skssqz<
- to_date(to_char(\'2014-12-31\',\'yyyy-mm-dd\'),\'yyyy-mm-dd\')+1)
- and(1=0orjks.kjrq>=to_date(\'2014-02-01\',\'yyyy-mm-dd\'))
- and(1=0orjks.kjrq<
- to_date(to_char(\'2015-03-31\',\'yyyy-mm-dd\'),\'yyyy-mm-dd\')+1)
- andjks.sjrq_1isnotnull
- andjks.zsuuid=yz.zsuuid
- andyz.tzlx_dmin(\'1\',\'4\')
- andyz.skcllx_dm=\'1\'
- andyz.skzl_dm<>\'20\'
- unionall
- selectwsz.djxh,
- yz.yzpzxhaszsuuid,
- yz.yzpzzl_dm,
- wsz.pzzl_dmaswspzzl_dm,
- wsz.pzzg_dmaswspzzg,
- wsz.pzhmaswspzhm,
- wsz.zsxm_dm,
- wsz.zspm_dm,
- wsz.sl_1assl,
- wsz.jsyj,
- wsz.sjjeasfpdksk,
- wsz.skssqq,
- wsz.skssqz,
- wsz.hy_dm,
- wsz.kjrqastfrq,
- wsz.sksx_dm
- fromhx_zs.zs_wsz wsz,hx_zs.zs_yjsf yz
- wherewsz.tzlx_dmin(\'1\',\'4\')
- andwsz.sksx_dmnotlike\'02%\'
- and(wsz.djxh=\'10106001062640079\'orwsz.kjdjxh=\'10106001062640079\')
- and(1=0orwsz.skssqq>=to_date(\'2014-11-01\',\'yyyy-mm-dd\'))
- and(1=0orwsz.skssqz<
- to_date(to_char(\'2014-12-31\',\'yyyy-mm-dd\'),\'yyyy-mm-dd\')+1)
- and(1=0orwsz.kjrq>=to_date(\'2014-02-01\',\'yyyy-mm-dd\'))
- and(1=0orwsz.kjrq<
- to_date(to_char(\'2015-03-31\',\'yyyy-mm-dd\'),\'yyyy-mm-dd\')+1)
- andwsz.zsuuid=yz.zsuuid
- andyz.tzlx_dmin(\'1\',\'4\')
- andyz.skcllx_dm=\'1\'
- andyz.skzl_dm<>\'20\')b
- groupbyzsxm_dm,
- zspm_dm,
- wspzzl_dm,
- wspzzg,
- wspzhm,
- zsuuid,
- sl,
- hy_dm,
- skssqq,
- skssqz,
- sksx_dm,
- tfrq,
- djxh,
- 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:
点击(此处)折叠或打开
- select b.zsxm_dm,
- b.zspm_dm,
- wspzzl_dm,
- wspzzg,
- wspzhm,
- b.zsuuid,
- sum(nvl(b.fpdksk, 0)) as fpdksk,
- sum(nvl(b.jsyj, 0)) as jsyj,
- sl,
- b.hy_dm,
- b.skssqq,
- b.skssqz,
- b.sksx_dm,
- tfrq,
- b.djxh,
- yz.yzpzzl_dm
- from ((select jks.djxh,
- jks.pzzl_dm as wspzzl_dm,
- jks.pzzg_dm as wspzzg,
- nvl(jks.pzhm, jks.dzsphm) as wspzhm,
- jks.zsxm_dm,
- jks.zspm_dm,
- jks.sl_1 as sl,
- jks.jsyj,
- jks.sjje as fpdksk,
- jks.skssqq,
- jks.skssqz,
- jks.hy_dm,
- jks.kjrq as tfrq,
- jks.sksx_dm,
- jks.zsuuid
- from hx_zs.zs_jks jks
- where jks.tzlx_dm in (\'1\', \'4\')
- and jks.sksx_dm not like \'02%\'
- and jks.skssswjg_dm=\'24401030000\'
- and (jks.kjdjxh = \'1016001062640079\' or jks.djxh = \'1016001062640079\')
- and (1 = 0 or jks.skssqq >= to_date(\'2014-11-01\', \'yyyy-mm-dd\'))
- and (1 = 0 or jks.skssqz <
- to_date(to_char(\'2014-12-31\', \'yyyy-mm-dd\'), \'yyyy-mm-dd\') + 1)
- and (1 = 0 or jks.kjrq >= to_date(\'2014-02-01\',\'yyyy-mm-dd\'))
- and (1 = 0 or jks.kjrq <
- to_date(to_char(\'2015-03-31\', \'yyyy-mm-dd\'), \'yyyy-mm-dd\') + 1)
- and jks.sjrq_1 is not null
- union all
- select wsz.djxh,
- wsz.pzzl_dm as wspzzl_dm,
- wsz.pzzg_dm as wspzzg,
- wsz.pzhm as wspzhm,
- wsz.zsxm_dm,
- wsz.zspm_dm,
- wsz.sl_1 as sl,
- wsz.jsyj,
- wsz.sjje as fpdksk,
- wsz.skssqq,
- wsz.skssqz,
- wsz.hy_dm,
- wsz.kjrq as tfrq,
- wsz.sksx_dm,
- wsz.zsuuid
- from hx_zs.zs_wsz wsz
- where wsz.tzlx_dm in (\'1\', \'4\')
- and wsz.sksx_dm not like \'02%\'
- and wsz.skssswjg_dm=\'24401030000\'
- and (wsz.djxh = \'1016001062640079\' or wsz.kjdjxh = \'1016001062640079\')
- and (1 = 0 or wsz.skssqq >= to_date(\'2014-11-01\',\'yyyy-mm-dd\'))
- and (1 = 0 or wsz.skssqz <
- to_date(to_char(\'2014-12-31\', \'yyyy-mm-dd\'), \'yyyy-mm-dd\') + 1)
- and (1 = 0 or wsz.kjrq >= to_date(\'2014-02-01\',\'yyyy-mm-dd\'))
- and (1 = 0 or wsz.kjrq <
- to_date(to_char(\'2015-03-31\', \'yyyy-mm-dd\'), \'yyyy-mm-dd\') + 1)) ) b , hx_zs.zs_yjsf yz
- where b.zsuuid = yz.zsuuid
- and yz.tzlx_dm in (\'1\', \'4\')
- and yz.skcllx_dm = \'1\'
- and yz.skzl_dm <> \'20\'
- group by b.zsxm_dm,
- b.zspm_dm,
- wspzzl_dm,
- wspzzg,
- wspzhm,
- b.zsuuid,
- sl,
- b.hy_dm,
- b.skssqq,
- b.skssqz,
- b.sksx_dm,
- tfrq,
- b.djxh,
- yzpzzl_dm;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29863023/viewspace-1472438/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29863023/viewspace-1472438/