SQL优化点滴之CBO统计信息

上周给客户远程解决了个SQL性能问题，一个SQL在测试环境需要7分钟、在正式环境需要15分钟：

select t1.f_entefsguid, t1.username, t1.f_bitycode, t1.billno ,to_char(MAX(pj.operationdate),'yyyy-mm-dd') as rq
from xxx.pj_billcheck t1, xxx.pj_businessmain pj, xxx.pj_businessdetail pjd
where pj.guid = pjd.f_bumaguid
and pj.f_butyguid ='pjcs'
and pjd.f_bitycode = t1.f_bitycode
and pjd.startno <= t1.billno
and pjd.endno >= t1.billno
and t1.f_entefsguid = '3309031239'
and t1.checksign='0'
group by t1.f_entefsguid, t1.username, t1.f_bitycode, t1.billno
order by f_entefsguid,username,f_bitycode,rq,billno

46596 rows selected.
Elapsed: 00:15:34.68

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=101 Card=1079 Bytes=85241)
   1    0   SORT (ORDER BY) (Cost=101 Card=1079 Bytes=85241)
   2    1     SORT (GROUP BY) (Cost=101 Card=1079 Bytes=85241)
   3    2       HASH JOIN (Cost=60 Card=1079 Bytes=85241)
   4    3         TABLE ACCESS (FULL) OF 'PJ_BUSINESSMAIN' (Cost=25 Card=1627 Bytes=34167)
   5    3         HASH JOIN (Cost=34 Card=2288 Bytes=132704)
   6    5           TABLE ACCESS (BY INDEX ROWID) OF 'PJ_BILLCHECK' (Cost=14 Card=552 Bytes=14352)
   7    6             INDEX (RANGE SCAN) OF 'IDX_BILLCHECK2' (NON-UNIQUE) (Cost=1 Card=1104)
   8    5           TABLE ACCESS (FULL) OF 'PJ_BUSINESSDETAIL' (Cost=19 Card=16570 Bytes=530240)

测试环境：

已选择46596行。

已用时间:  00: 06: 56.09

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=90 Card=1079 Bytes=85241)
   1    0   SORT (ORDER BY) (Cost=90 Card=1079 Bytes=85241)
   2    1     SORT (GROUP BY) (Cost=90 Card=1079 Bytes=85241)
   3    2       HASH JOIN (Cost=60 Card=1079 Bytes=85241)
   4    3         TABLE ACCESS (FULL) OF 'PJ_BUSINESSMAIN' (Cost=25 Card=1627 Bytes=34167)
   5    3         HASH JOIN (Cost=34 Card=2288 Bytes=132704)
   6    5           TABLE ACCESS (BY INDEX ROWID) OF 'PJ_BILLCHECK' (Cost=14 Card=552 Bytes=14352)
   7    6             INDEX (RANGE SCAN) OF 'IDX_BILLCHECK2' (NON-UNIQUE) (Cost=1 Card=1104)
   8    5           TABLE ACCESS (FULL) OF 'PJ_BUSINESSDETAIL' (Cost=19 Card=16570 Bytes=530240)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2707  consistent gets
        821  physical reads
          0  redo size
    1308448  bytes sent via SQL*Net to client
      34669  bytes received via SQL*Net from client
       3108  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      46596  rows processed

对比发现执行计划没有什么变化，优化SQL的时候，我基本上是从查看统计信息开始，检查了一下统计信息，发现这个用户下的对象都没有统计信息，因为客户的系统运行了一段时间了，为了减少对系统的影响，我只对这个SQL涉及到的对象做了统计信息收集：

analyze table xxxx.pj_billcheck compute statistics for table for all indexes for all indexed columns;

analyze table xxx.pj_businessmain compute statistics for table for all indexes for all indexed columns;

analyze table xxx.pj_businessdetail compute statistics for table for all indexes for all indexed columns;

收集统计信息后运行SQL，只需要12秒就运行完了，客户能接受这个结果，优化结束。
Tips：优化是无止境的，当达到客户的要求（签合同了就叫做SLA）就立即停止，因为往后的代价成指数增长，性能收益呈指数下降！
   这个SQL的优化很简单，就做了相关对象上的统计信息收集，性能就提高了75倍，有统计信息就万事大吉了吗？还是这个客户，过来两天又找到我，这次有点不同了，如何优化，且听下回分解:)