主页 > 知识库 > 数据库 > SQL server >

SQL优化点滴之SQL改写

来源:网络 作者:itpub.com.cn 发表于:2012-11-22 10:38  点击:
前面一篇用一个例子展示的统计信息对于CBO的重要性,但统计信息并不是SQL优化的全部,这次我们来看同一个客户的另外一个例子,开始之前先说点 别的,我跟客户解释了统计信息的重要性,同时也忠告客户在一个稳定的系统中不要轻易的做出改变,但是客户还是没忍
前面一篇用一个例子展示的统计信息对于CBO的重要性,但统计信息并不是SQL优化的全部,这次我们来看同一个客户的另外一个例子,开始之前先说点 别的,我跟客户解释了统计信息的重要性,同时也忠告客户在一个稳定的系统中不要轻易的做出改变,但是客户还是没忍住,给这个用户下的所有对象都收集了统计 信息,这不第二天又找到我说这个系统登录的时候比之前慢了许多,并告知我他收集了统计信息后才出现这个情况的,呵呵,昨天的忠告估计是忘记了.....删 除统计信息后系统登录正常!

我们开始吧,先看一下这个SQL的执行计划:


  1. select t1.f_enteguid,
  2.                        t1.makeusername,
  3.                        t1.f_bitycode,
  4.                        t3.bityname,
  5.                        t1.startno,
  6.                        to_char(MAX(pj.operationdate), 'yyyy-mm-dd') as rq,
  7.                        decode(t1.billstatus,
  8.                               '1',
  9.                               decode(t3.usepayment,
  10.                                      '1',
  11.                                      decode(t1.capitalmode, '1', t1.totalmoney),
  12.                                      t1.totalmoney),
  13.                               0) as totalmoney
  14.                   from fszg.zs_billinfo t1,
  15.                        fszg.pj_businessmain pj,
  16.                        fszg.pj_businessdetail pjd,
  17.                        fszg.pj_billtype t3
  18.                  where pj.guid = pjd.f_bumaguid
  19.                    and pj.f_butyguid = 'pjcs'
  20.                    and pjd.f_bitycode = t1.f_bitycode
  21.                    and pjd.startno <= t1.startno
  22.                    and pjd.endno >= t1.startno
  23.                    and t1.f_bitycode = t3.bitycode
  24.                    and t1.f_enteguid = '3309031239'
  25.                    and nvl(t1.checkflag, 0) = 0
  26.                  group by t1.f_enteguid,
  27.                           t1.makeusername,
  28.                           t1.f_bitycode,
  29.                           t3.bityname,
  30.                           t1.startno,
  31.                           t1.billstatus,
  32.                           t3.usepayment,
  33.                           t1.capitalmode,
  34.                           t1.totalmoney
  35.                  order by f_enteguid,
  36.                           makeusername,
  37.                           f_bitycode,
  38.                           bityname,
  39.                           rq,
  40.                           startno
  41.  
  42.  
  43. 14:15:35 SQL> /
  44.  
  45. 33314 rows selected.
  46.  
  47. Elapsed: 00:13:28.89
  48.  
  49. Execution Plan
  50. ----------------------------------------------------------
  51.    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=504 Card=16 Bytes=2192)
  52.    1 0 SORT (ORDER BY) (Cost=504 Card=16 Bytes=2192)
  53.    2 1 SORT (GROUP BY) (Cost=504 Card=16 Bytes=2192)
  54.    3 2 HASH JOIN (Cost=485 Card=140 Bytes=19180)
  55.    4 3 TABLE ACCESS (FULL) OF 'PJ_BILLTYPE' (Cost=2 Card=146 Bytes=5840)
  56.    5 3 HASH JOIN (Cost=482 Card=141 Bytes=13677)
  57.    6 5 HASH JOIN (Cost=414 Card=1083 Bytes=79059)
  58.    7 6 TABLE ACCESS (BY INDEX ROWID) OF 'ZS_BILLINFO' (Cost=321 Card=51 Bytes=1887)
  59.    8 7 INDEX (RANGE SCAN) OF 'IDX_F_ENTEGUID' (NON-UNIQUE) (Cost=15 Card=5079)
  60.    9 6 TABLE ACCESS (FULL) OF 'PJ_BUSINESSDETAIL' (Cost=92 Card=96365 Bytes=3469140)
  61.   10 5 TABLE ACCESS (FULL) OF 'PJ_BUSINESSMAIN' (Cost=67 Card=6016 Bytes=144384)
  62.  
  63. Statistics
  64. ----------------------------------------------------------
  65.           0 recursive calls
  66.           0 db block gets
  67.        3150 consistent gets
  68.           0 physical reads
  69.           0 redo size
  70.     1199399 bytes sent via SQL*Net to client
  71.       25115 bytes received via SQL*Net from client
  72.        2222 SQL*Net roundtrips to/from client
  73.           2 sorts (memory)
  74.           0 sorts (disk)
  75.       33314 rows processed
  76.  
  77. 14:29:18 SQL>
这次检查表上的统计信息是完整的,怎么去做优化呢?我们先来看看这个几个表上的数据量:


 
  1. PJ_BILLTYPE 146行
  2. PJ_BUSINESSDETAIL 96365行
  3. PJ_BUSINESSMAIN 42550行
  4. ZS_BILLINFO 3083006行
我们在来看看这个执行计划合理么,通常我们希望先处理小的对象,在处理大的对象,我们先看一下这几个表的连接条件:

限制条件:

执行计划图示:


实际的执行计划跟我们期望的背离了,我们期望小的结果集先处理,结果两个最大的结果集先处理了,根据连接条件,我们期望的执行计划是pjd、pj先做连接,然后再跟t1做连接或者是跟t1、t3连接后的结果集做连接最后返回结果集,根据这个预期,我们改写SQL如下:


 
  1. with a as
  2. (
  3. select pj.operationdate,pjd.f_bitycode,pjd.startno, pjd.endno
  4. from fszg.pj_businessmain pj,fszg.pj_businessdetail pjd
  5. where 1=1
  6. and pj.guid = pjd.f_bumaguid
  7. and pj.f_butyguid = 'pjcs'
  8. )
  9. select t1.f_enteguid,
  10.                        t1.makeusername,
  11.                        t1.f_bitycode,
  12.                        t3.bityname,
  13.                        t1.startno,
  14.                        MAX(a.operationdate) as rq,
  15.                        decode(t1.billstatus,
  16.                               '1',
  17.                               decode(t3.usepayment,
  18.                                      '1',
  19.                                      decode(t1.capitalmode, '1', t1.totalmoney),
  20.                                      t1.totalmoney),
  21.                               0) as totalmoney
  22.                   from fszg.zs_billinfo t1,
  23.                        a,
  24.                        fszg.pj_billtype t3
  25.                  where 1=1
  26.                    and a.startno <= t1.startno
  27.                   and a.endno >= t1.startno
  28.                    and t1.f_bitycode = t3.bitycode
  29.                    and t1.f_enteguid = '3309031239'
  30.                    and nvl(t1.checkflag, 0) = 0
  31.                  group by t1.f_enteguid,
  32.                           t1.makeusername,
  33.                           t1.f_bitycode,
  34.                           t3.bityname,
  35.                           t1.startno,
  36.                           t1.billstatus,
  37.                           t3.usepayment,
  38.                           t1.capitalmode,
  39.                           t1.totalmoney
  40.                  order by f_enteguid,
  41.                           makeusername,
  42.                           f_bitycode,
  43.                           bityname,
  44.                           rq,
  45.                           startno

改写后的SQL执行计划如下:

  1. 33314 rows selected.
  2.  
  3. Elapsed: 00:01:24.68
  4.  
  5. Execution Plan
  6. ----------------------------------------------------------
  7.    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=666 Card=16 Bytes=2128)
  8.    1 0 SORT (ORDER BY) (Cost=666 Card=16 Bytes=2128)
  9.    2 1 SORT (GROUP BY) (Cost=666 Card=16 Bytes=2128)
  10.    3 2 HASH JOIN (Cost=619 Card=1587 Bytes=211071)
  11.    4 3 TABLE ACCESS (FULL) OF 'PJ_BILLTYPE' (Cost=2 Card=146 Bytes=5840)
  12.    5 3 MERGE JOIN (Cost=616 Card=1588 Bytes=147684)
  13.    6 5 SORT (JOIN) (Cost=287 Card=12508 Bytes=700448)
  14.    7 6 HASH JOIN (Cost=168 Card=12508 Bytes=700448)
  15.    8 7 TABLE ACCESS (FULL) OF 'PJ_BUSINESSMAIN' (Cost=67 Card=6016 Bytes=144384)
  16.    9 7 TABLE ACCESS (FULL) OF 'PJ_BUSINESSDETAIL' (Cost=92 Card=96365 Bytes=3083680)
  17.   10 5 FILTER
  18.   11 10 SORT (JOIN)
  19.   12 11 TABLE ACCESS (BY INDEX ROWID) OF 'ZS_BILLINFO'(Cost=321 Card=51 Bytes=1887)
  20.   13 12 INDEX (RANGE SCAN) OF 'IDX_F_ENTEGUID' (NON-UNIQUE) (Cost=15 Card=5079)
  21.  
  22.  
  23.  
  24. Statistics
  25. ----------------------------------------------------------
  26.           0 recursive calls
  27.           0 db block gets
  28.        3101 consistent gets
  29.           0 physical reads
  30.           0 redo size
  31.     1199387 bytes sent via SQL*Net to client
  32.       25091 bytes received via SQL*Net from client
  33.        2222 SQL*Net roundtrips to/from client
  34.           4 sorts (memory)
  35.           0 sorts (disk)
  36.       33314 rows processed
  37.  
  38. 16:17:22 SQL>
图示一下执行计划:

嘿,基本上按照我们期望的执行了,效果如何呢:
从原来的13分钟减少到1分多钟,性能提高将近10倍!

    有帮助
    (0)
    0%
    没帮助
    (0)
    0%