设置统计信息优化案例

    目前看到好多DBA优化时候，都是慢了咋办？ 答曰 首先收集统计信息然后再.....。  这个答案不是很准确，我一直认为是先分析出性能瓶颈的地方，然后针对性的做调整措施。今天这个案例就是统计信息最新，但是执行的还是慢。另外这个案例可以延伸出 如何在线上迅速改变SQL的执行计划， 如果SQl的执行计划不稳定，如何固定。hash ，NL 关联适用条件等等...  知识点有点多。。。慢慢看吧， 想想，走走，看看，总能看到不一样的风景。

SQL：select b.*   from  arch_info b  , arch_stockoutmap a, arch_stockout c

      where      b.archid = a.archid    and c.batchID = a.batchID    and c.status = '1'  and b.state = '2';

执行计划：

分析：

    查询下表，发现arch_stockout ，2500条数据， arch_stockoutmap  不大，100M左右。

arch_info 很大 8G。 

主要性能问题就 8G的大表全表扫描，并且hash 关联。 

于是直接查询发现最后结果只返回 9条数据，心里已经有优化思路。调查下数据量arch_stockout c ， 中c.status = '1'有2400条左右。 添加hints

select /*+ use_nl(c,a,b)  leading(c) */   b.* from  arch_info b  , arch_stockoutmap a, arch_stockout c....... 执行计划以NL为主，c  Nl  a  结果集再NL b。3S执行完。

     他说是满足要求了，但是需要固定执行计划， 不需要添加hints，毕竟是前台页面执行的。

寻找根本原因：

   开始调查表的统计信息，直方图，发现都没有过期。我还是收集了下，但是还是走hash ,大表全表扫描。根据调查发现

      select  COUNT(1)    from  arch_stockoutmap a, arch_stockout c

      Where   c.batchID = a.batchID   and c.status = '1';  ----有50万条记录。

 也就是说 c 表根据条件c.status = '1'; 有2400条数据，但是关联a 表其结果有50万。

（此时C表和 A表是hash 关联）。所以CBO选择了和最大的B表 hash 关联。

  实验结果出来了，的确走NL效率比较好，但是统计信息，直方图都是刚刚收集的就是走不了NL？这种怎么破？？

  此时我们看执行计划的源头 ID = 2

这边返回 2400条。如果这边返回1条数据呢？ 那肯定走NL了， 于是我用hints 

/*+ cardinality(c,1)  */测试下原来的SQl。 果然和我预想的一样。 执行计划以NL为主，c Nl a结果集再NL b。  那么删除c的统计就行了。让CBO估算这边的rows 为1或者0，反正这边的rows 是越小越好。 于是我就删除了C表的统计信息。预想着差不多了，但是explain  plan  for 原来的SQl，发现还是hash。 细致一看发现是动态采样惹的祸。那我就不让他动态采样，接着我设置了C表的统计信息。

 begin 

 DBMS_STATS.set_table_stats(ownname => 'DAXT',tabname => 'ARCH_STOCKOUT' ,numrows => '3');

 end;

设置完成后，在 explain  plan  for 果然执行计划以NL为主，c Nl a结果集再NL b。

3S执行完， 结果9条数据。

大家不要以为这样就完了， 最后还要锁定该表的统计信息。可以猜想下，为什么锁定？ 

做DBA莫，肯定需要精打细算，锁定后有什么缺点，以及优点。 优点的好处> 缺点的坏处。这种事情才能干。