前面一篇用一个例子展示的统计信息对于CBO的重要性，但统计信息并不是SQL优化的全部，这次我们来看同一个客户的另外一个例子，开始之前先说点别的，我跟客户解释了统计信息的重要性，同时也忠告客户在一个稳定的系统中不要轻易的做出改变，但是客户还是没忍住，给这个用户下的所有对象都收集了统计信息，这不第二天又找到我说这个系统登录的时候比之前慢了许多，并告知我他收集了统计信息后才出现这个情况的，呵呵，昨天的忠告估计是忘记了.....删除统计信息后系统登录正常!
我们开始吧，先看一下这个SQL的执行计划：
- select t1.f_enteguid,
- t1.makeusername,
- t1.f_bitycode,
- t3.bityname,
- t1.startno,
- to_char(MAX(pj.operationdate), 'yyyy-mm-dd') as rq,
- decode(t1.billstatus,
- '1',
- decode(t3.usepayment,
- '1',
- decode(t1.capitalmode, '1', t1.totalmoney),
- t1.totalmoney),
- 0) as totalmoney
- from fszg.zs_billinfo t1,
- fszg.pj_businessmain pj,
- fszg.pj_businessdetail pjd,
- fszg.pj_billtype t3
- where pj.guid = pjd.f_bumaguid
- and pj.f_butyguid = 'pjcs'
- and pjd.f_bitycode = t1.f_bitycode
- and pjd.startno <= t1.startno
- and pjd.endno >= t1.startno
- and t1.f_bitycode = t3.bitycode
- and t1.f_enteguid = '3309031239'
- and nvl(t1.checkflag, 0) = 0
- group by t1.f_enteguid,
- t1.makeusername,
- t1.f_bitycode,
- t3.bityname,
- t1.startno,
- t1.billstatus,
- t3.usepayment,
- t1.capitalmode,
- t1.totalmoney
- order by f_enteguid,
- makeusername,
- f_bitycode,
- bityname,
- rq,
- startno
- 14:15:35 SQL> /
- 33314 rows selected.
- Elapsed: 00:13:28.89
- Execution Plan
- ----------------------------------------------------------
- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=504 Card=16 Bytes=2192)
- 1 0 SORT (ORDER BY) (Cost=504 Card=16 Bytes=2192)
- 2 1 SORT (GROUP BY) (Cost=504 Card=16 Bytes=2192)
- 3 2 HASH JOIN (Cost=485 Card=140 Bytes=19180)
- 4 3 TABLE ACCESS (FULL) OF 'PJ_BILLTYPE' (Cost=2 Card=146 Bytes=5840)
- 5 3 HASH JOIN (Cost=482 Card=141 Bytes=13677)
- 6 5 HASH JOIN (Cost=414 Card=1083 Bytes=79059)
- 7 6 TABLE ACCESS (BY INDEX ROWID) OF 'ZS_BILLINFO' (Cost=321 Card=51 Bytes=1887)
- 8 7 INDEX (RANGE SCAN) OF 'IDX_F_ENTEGUID' (NON-UNIQUE) (Cost=15 Card=5079)
- 9 6 TABLE ACCESS (FULL) OF 'PJ_BUSINESSDETAIL' (Cost=92 Card=96365 Bytes=3469140)
- 10 5 TABLE ACCESS (FULL) OF 'PJ_BUSINESSMAIN' (Cost=67 Card=6016 Bytes=144384)
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 3150 consistent gets
- 0 physical reads
- 0 redo size
- 1199399 bytes sent via SQL*Net to client
- 25115 bytes received via SQL*Net from client
- 2222 SQL*Net roundtrips to/from client
- 2 sorts (memory)
- 0 sorts (disk)
- 33314 rows processed
- 14:29:18 SQL>
这次检查表上的统计信息是完整的，怎么去做优化呢？我们先来看看这个几个表上的数据量：
- PJ_BILLTYPE 146行
- PJ_BUSINESSDETAIL 96365行
- PJ_BUSINESSMAIN 42550行
- ZS_BILLINFO 3083006行
我们在来看看这个执行计划合理么，通常我们希望先处理小的对象，在处理大的对象，我们先看一下这几个表的连接条件：
限制条件：
执行计划图示：
实际的执行计划跟我们期望的背离了，我们期望小的结果集先处理，结果两个最大的结果集先处理了，根据连接条件，我们期望的执行计划是pjd、pj先做连接，然后再跟t1做连接或者是跟t1、t3连接后的结果集做连接最后返回结果集，根据这个预期，我们改写SQL如下：
- with a as
- (
- select pj.operationdate,pjd.f_bitycode,pjd.startno, pjd.endno
- from fszg.pj_businessmain pj,fszg.pj_businessdetail pjd
- where 1=1
- and pj.guid = pjd.f_bumaguid
- and pj.f_butyguid = 'pjcs'
- )
- select t1.f_enteguid,
- t1.makeusername,
- t1.f_bitycode,
- t3.bityname,
- t1.startno,
- MAX(a.operationdate) as rq,
- decode(t1.billstatus,
- '1',
- decode(t3.usepayment,
- '1',
- decode(t1.capitalmode, '1', t1.totalmoney),
- t1.totalmoney),
- 0) as totalmoney
- from fszg.zs_billinfo t1,
- a,
- fszg.pj_billtype t3
- where 1=1
- and a.startno <= t1.startno
- and a.endno >= t1.startno
- and t1.f_bitycode = t3.bitycode
- and t1.f_enteguid = '3309031239'
- and nvl(t1.checkflag, 0) = 0
- group by t1.f_enteguid,
- t1.makeusername,
- t1.f_bitycode,
- t3.bityname,
- t1.startno,
- t1.billstatus,
- t3.usepayment,
- t1.capitalmode,
- t1.totalmoney
- order by f_enteguid,
- makeusername,
- f_bitycode,
- bityname,
- rq,
- startno
改写后的SQL执行计划如下：
- 33314 rows selected.
- Elapsed: 00:01:24.68
- Execution Plan
- ----------------------------------------------------------
- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=666 Card=16 Bytes=2128)
- 1 0 SORT (ORDER BY) (Cost=666 Card=16 Bytes=2128)
- 2 1 SORT (GROUP BY) (Cost=666 Card=16 Bytes=2128)
- 3 2 HASH JOIN (Cost=619 Card=1587 Bytes=211071)
- 4 3 TABLE ACCESS (FULL) OF 'PJ_BILLTYPE' (Cost=2 Card=146 Bytes=5840)
- 5 3 MERGE JOIN (Cost=616 Card=1588 Bytes=147684)
- 6 5 SORT (JOIN) (Cost=287 Card=12508 Bytes=700448)
- 7 6 HASH JOIN (Cost=168 Card=12508 Bytes=700448)
- 8 7 TABLE ACCESS (FULL) OF 'PJ_BUSINESSMAIN' (Cost=67 Card=6016 Bytes=144384)
- 9 7 TABLE ACCESS (FULL) OF 'PJ_BUSINESSDETAIL' (Cost=92 Card=96365 Bytes=3083680)
- 10 5 FILTER
- 11 10 SORT (JOIN)
- 12 11 TABLE ACCESS (BY INDEX ROWID) OF 'ZS_BILLINFO'(Cost=321 Card=51 Bytes=1887)
- 13 12 INDEX (RANGE SCAN) OF 'IDX_F_ENTEGUID' (NON-UNIQUE) (Cost=15 Card=5079)
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 3101 consistent gets
- 0 physical reads
- 0 redo size
- 1199387 bytes sent via SQL*Net to client
- 25091 bytes received via SQL*Net from client
- 2222 SQL*Net roundtrips to/from client
- 4 sorts (memory)
- 0 sorts (disk)
- 33314 rows processed
- 16:17:22 SQL>
图示一下执行计划：
嘿，基本上按照我们期望的执行了，效果如何呢：
从原来的13分钟减少到1分多钟，性能提高将近10倍！
阅读(3010) | 评论(0) | 转发(2) |
