4月2日 索引危机（2）索引范围扫描的优化方法

昨天下午又优化了几个SQL,对于这几个SQL来说，优化后都有了明显的性能提升，不过从今天上午10点多观察到的CPU使用率来看，好像变化不大。由于排队效应的存在，简单的减法并不能完全解决问题。以前排在前面的几个SQL解决了以后，又冒出了几条新的SQL，其中一条SQL每次执行的BUFFER GET数量高达3万多：

select a.filename, b.source_id, b.source_path, b.localnet_abbr, b.file_fmt,b.is_txt_fmt, a.deal_flag       

from SCH_XXX a, BBB b 

where (a.deal_flag='W' or a.deal_flag='B') and

 a.validflag='Y' and b.pipe_id=:szPipeId and a.source_id=b.source_id

从执行计划上看：

--------------------------------------------------------------------------------

| Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost |

--------------------------------------------------------------------------------

|SELECT STATEMENT                |----- 1104374539 ----|       |      |   3486 |

|HASH JOIN                       |                     |    79K|    7M|   3486 |

| TABLE ACCESS BY INDEX ROWID    |BBBBB                |     2 |   98 |      2 |

|  INDEX RANGE SCAN              |FK_SOURCE_WORKFLOW   |     1 |      |      1 |

| TABLE ACCESS FULL              |SCH_XXXXX             |     1M|   61M|   3474 |

--------------------------------------------------------------------------------

从这个执行计划上看，对SCH_XXXX的扫描是开销最大的，这张表中扫描的记录数位100万（1M）。通过SPREPSQL报告，我们看到：

                     Statement Total      Per Execute  

                     ---------------  ---------------  

        Buffer Gets:         626,984         39,186.5  

         Disk Reads:         425,693         26,605.8  

     Rows processed:              46              2.9

     CPU Time(s/ms):              50          3,113.1

 Elapsed Time(s/ms):             988         61,779.7

              Sorts:               0               .0

        Parse Calls:              16              1.0

      Invalidations:               0

      Version count:               2

    Sharable Mem(K):              29

         Executions:              16

平均每次执行返回的记录数是2.9条，也就是说真正符合条件的记录只有区区3条左右。这和执行计划中的79K相差甚远，于是我检查了一下这张表，这张表总计有200万条记录。在这张表上，有两个过滤条件(a.deal_flag='W' or a.deal_flag='B') and  a.validflag='Y'。我首先分析了一下validflag：

Select validflag,count(*) from sch_xxxx group by validflag;

发现表中的大多数记录都是validflag='Y'的记录，这个字段的选择性是很差的，于是我又检查了一下deal_flag，发现绝大多数记录都是deal_flag='Y'的，其他的记录十分少，有时候甚至是0。这是一个典型的字段倾斜的案例，凑巧的是在这个SQL种并没有使用绑定变量，于是在deal_flag上创建一个索引，并且分析一下柱状图就可以解决问题。可客户协商了一下，这张表只有200万记录，可以立即创建索引，并做表分析，于是我马上执行了下面的脚本：

CREATE INDEX SHCJ.IDX_DEALFLAG ON SHCJ.SCH_XXXX(DEAL_FLAG) ;

Exec DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SHCJ', tabname=> 'SCH_XXXX', estimate_percent=>30, method_opt=>'for all indexed columns size skewonly',cascade=>true, degree=>2);

索引创建后，再次分析了表和索引，其中表中对索引字段中倾斜的字段分析了柱状图。Method_opt也可以设为'for all columns size auto'，这是10G的缺省，而9i的缺省是'for all columns size 1'，也就是不分析柱状图。

上诉操作完成后，我马上最这个SQL进行了分析，发现BUFFER GET的数量从近4万减少到了10.7，这个SQL产生的物理读基本上没有了。这说明和我判断的一致，这个索引起到了很好的作用。

从上午业务高峰期的情况来看，虽然又调整了3/4个SQL，不过总体的效果并没有很明显的显现。上午10点-11点30之间，系统CPU使用率仍然在95-100%高位，平均事务响应时间虽然有了20%左右的提升，但是感觉还是很难彻底解决问题。在STATSPACK报告中，已经找不到明显的可以通过索引调整进行优化的SQL了，如何解决CPU负荷过高的问题，好像还是没有半点眉目。这套系统的SQL开销比较平均，除了排在BUFFER GET第一位的那条SQL占了整个开销的37%外，其他的SQL占整个系统BUFFER GET的比例都不高，最多的也只是在4%左右。调整这些SQL，虽然能够取得一些效果，不过效果还是不明显。看样子如果想要走捷径，必须优化这个排名TOP 1的SQL。于是我再次看了看这个SQL，这条SQL涉及到两张表，一张稍微大点的表T_PRODUCT_INFO，这张表的WHERE条件中有一个PRODUCT_TYPE=:P1 AND PRODUCT_CLASS=:P2的过滤条件，另外一张表是涨小表，大概只有20来条记录。执行计划是扫描完两张表后，结果集做HASH JOIN。在T_PRODUCT_INFO表，有一个复合索引（PRODUCT_TYPE,PRODUCT_CLASS），执行计划也是通过对这个索引做范围扫描的。从执行计划上来看，是没有问题的。我看了一下，平均每次返回结果的记录数不过60多条。而T_PRODUCT_INFO做索引范围扫描找到的记录是接近700条。T_PRODUCT_INFO表的记录数为800万，从一个800万记录的表中，扫描出700条记录，这个索引的效率还是比较高的。为什么这么一个简单的SQL会产生1500多个BUFFER GET呢？我马上联想到了CLUSTER FACTOR，于是马上查了DBA_INDEXES，果然，这个索引的CLUSTER FACTOR接近记录的数量。CLUSTER FACTOR较高，在通过索引范围扫描访问表的时候，由于每个索引键值更多的指向新数据块，因此会产生更多的物理IO,并导致BUFFER GET的数量增加。

我马上找到郭工，了解这张表的用途。经过了解，这张表是存放产品的资料的，在做售后服务的时候，会通过PRODUCT_TYPE和PRODUCT_CLASS，找到符合条件的备件，并根据库存情况以及本次服务的其他限定条件，查出满足条件的备件。这张表的数据是逐步添加的，随着新的产品及其备件的出现，这张表的数据也越来越多。以前这张表只有1/200万条记录，随着这几年的积累，记录数已经接近100万了。我想了一下，这张表的记录的加入，肯定不会按照PRODUCT_TYPE和PRODUCT_CLASS进行排序，因此这个索引的CLUSTER FACTOR比较大也就不足为奇了。于是我问郭工，对于这张表的访问，是不是大多数都是通过PRODUCT_TYPE和PRODUCT_CLASS进行，郭工想了想，说：“是的，绝大多数访问这张表都是根据这两个字段的条件，另外一种就是根据PRODUCT_ID直接定位产品。”PRODUCT_ID是这张表的主键，既然这张表主要的访问方式只有这两种，那么我倒是想出了一个优化方案。

由于这张表记录的插入顺序没有按照PRODUCT_TYPE和PRODUCT_CLASS排序，所以索引范围扫描的成本较高。如果我们能够对这张表进行重组，使之按照这两个字段排序，那么范围扫描的成本就可以得到有效的减少。采用如下的方法可以完成这个优化：

1、将原表rename为t_product_info_old

2、Create table t_product_info as select *  from t_product_info old order by product_type,product_class；

3、创建相关索引及约束关系

4、分析表和索引

我和郭工商量了一下能否今晚实施一下这个优化操作。郭工听我说了半天CLUSTER FACTOR，也没听明白。他说：“老白，技术的问题我就不管了，我帮你申请一下今晚停机的事情。这套系统晚上就只有一些统计报表，所以晚上19点到21点之间都是可以停库的。如果你的操作能够在2个小时内完成，停机申请就没任何问题，否则我就需要和业务部门协商了。我想了想，这张表也就几百M的大小，1个小时肯定能解决问题了。