一条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/