Chinaunix首页 | 论坛 | 博客
  • 博客访问: 402055
  • 博文数量: 85
  • 博客积分: 1416
  • 博客等级: 上尉
  • 技术积分: 916
  • 用 户 组: 普通用户
  • 注册时间: 2007-10-03 20:35
文章分类
文章存档

2014年(5)

2012年(2)

2010年(3)

2009年(67)

2008年(8)

分类:

2012-11-22 14:25:04

原文地址:SQL优化点滴之SQL改写 作者:itpub.com.cn

前面一篇用一个例子展示的统计信息对于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. 14:15:35 SQL> /

  42. 33314 rows selected.

  43. Elapsed: 00:13:28.89

  44. Execution Plan
  45. ----------------------------------------------------------
  46.    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=504 Card=16 Bytes=2192)
  47.    1 0 SORT (ORDER BY) (Cost=504 Card=16 Bytes=2192)
  48.    2 1 SORT (GROUP BY) (Cost=504 Card=16 Bytes=2192)
  49.    3 2 HASH JOIN (Cost=485 Card=140 Bytes=19180)
  50.    4 3 TABLE ACCESS (FULL) OF 'PJ_BILLTYPE' (Cost=2 Card=146 Bytes=5840)
  51.    5 3 HASH JOIN (Cost=482 Card=141 Bytes=13677)
  52.    6 5 HASH JOIN (Cost=414 Card=1083 Bytes=79059)
  53.    7 6 TABLE ACCESS (BY INDEX ROWID) OF 'ZS_BILLINFO' (Cost=321 Card=51 Bytes=1887)
  54.    8 7 INDEX (RANGE SCAN) OF 'IDX_F_ENTEGUID' (NON-UNIQUE) (Cost=15 Card=5079)
  55.    9 6 TABLE ACCESS (FULL) OF 'PJ_BUSINESSDETAIL' (Cost=92 Card=96365 Bytes=3469140)
  56.   10 5 TABLE ACCESS (FULL) OF 'PJ_BUSINESSMAIN' (Cost=67 Card=6016 Bytes=144384)

  57. Statistics
  58. ----------------------------------------------------------
  59.           0 recursive calls
  60.           0 db block gets
  61.        3150 consistent gets
  62.           0 physical reads
  63.           0 redo size
  64.     1199399 bytes sent via SQL*Net to client
  65.       25115 bytes received via SQL*Net from client
  66.        2222 SQL*Net roundtrips to/from client
  67.           2 sorts (memory)
  68.           0 sorts (disk)
  69.       33314 rows processed

  70. 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. Elapsed: 00:01:24.68

  3. Execution Plan
  4. ----------------------------------------------------------
  5.    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=666 Card=16 Bytes=2128)
  6.    1 0 SORT (ORDER BY) (Cost=666 Card=16 Bytes=2128)
  7.    2 1 SORT (GROUP BY) (Cost=666 Card=16 Bytes=2128)
  8.    3 2 HASH JOIN (Cost=619 Card=1587 Bytes=211071)
  9.    4 3 TABLE ACCESS (FULL) OF 'PJ_BILLTYPE' (Cost=2 Card=146 Bytes=5840)
  10.    5 3 MERGE JOIN (Cost=616 Card=1588 Bytes=147684)
  11.    6 5 SORT (JOIN) (Cost=287 Card=12508 Bytes=700448)
  12.    7 6 HASH JOIN (Cost=168 Card=12508 Bytes=700448)
  13.    8 7 TABLE ACCESS (FULL) OF 'PJ_BUSINESSMAIN' (Cost=67 Card=6016 Bytes=144384)
  14.    9 7 TABLE ACCESS (FULL) OF 'PJ_BUSINESSDETAIL' (Cost=92 Card=96365 Bytes=3083680)
  15.   10 5 FILTER
  16.   11 10 SORT (JOIN)
  17.   12 11 TABLE ACCESS (BY INDEX ROWID) OF 'ZS_BILLINFO'(Cost=321 Card=51 Bytes=1887)
  18.   13 12 INDEX (RANGE SCAN) OF 'IDX_F_ENTEGUID' (NON-UNIQUE) (Cost=15 Card=5079)



  19. Statistics
  20. ----------------------------------------------------------
  21.           0 recursive calls
  22.           0 db block gets
  23.        3101 consistent gets
  24.           0 physical reads
  25.           0 redo size
  26.     1199387 bytes sent via SQL*Net to client
  27.       25091 bytes received via SQL*Net from client
  28.        2222 SQL*Net roundtrips to/from client
  29.           4 sorts (memory)
  30.           0 sorts (disk)
  31.       33314 rows processed

  32. 16:17:22 SQL>
图示一下执行计划:

嘿,基本上按照我们期望的执行了,效果如何呢:
从原来的13分钟减少到1分多钟,性能提高将近10倍!
阅读(1709) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~