4月1日 索引危机（1）不能修改应用的项目

刚才在会议室里和郭工一起讨论了这个项目，最近一两个月，郭工他们这套系统的CPU使用率增加了20%左右，白天业务高峰的时候，基本上CPU都在90-100%，IDLE基本上都是0。郭工感觉到系统的风险很大，于是向领导李总做了汇报。于是李总就急忙叫我过来帮他们分析分析。

听郭工介绍，这套系统2周后将迎来每个月最繁忙的时段，从目前的情况来看，12个CPU 24核的系统，r队列已经达到了40多，如果不做一些优化，可能很难安全渡过几天后的业务高峰。想要大幅度降低CPU的使用率，最好的办法是优化应用。

于是郭工把开发商负责维护系统的组长也叫到了会议室，商讨修改应用的事情。开发商一听说要修改SQL，马上把头摇得跟拨浪鼓似地。在2周时间内完成SQL修改工作，对于开发商来说，确实难度不小。经过协商，开发商认为索引的修改和PL/SQL存储过程的优化他们马上能够配合我们做，而需要修改程序的变更，他们必须上报给公司，走公司的变更流程，在2周内完成这个工作基本上不可能。开发商看样子是指望不上了，于是我决定把优化的重点放在索引的调整上。如果能找到几个buffer get较大的SQL,优化一下SQL，让CPU使用率下降10-15%，基本上能够完成任务了。

我采集了一下10点到11点的statspack报告:

Cache Sizes (end)

~~~~~~~~~~~~~~~~~

               Buffer Cache:     8,000M      Std Block Size:          8K

           Shared Pool Size:     3,200M          Log Buffer:     10,240K

Load Profile

~~~~~~~~~~~~                            Per Second       Per Transaction

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

                  Redo size:          2,596,262.30             60,116.45

              Logical reads:            572,843.36             13,264.19

              Block changes:             22,523.53                521.53

             Physical reads:              2,637.11                 61.06

            Physical writes:                672.75                 15.58

                 User calls:             21,920.49                507.57

                     Parses:              6,339.75                146.80

                Hard parses:                310.56                  7.19

                      Sorts:              1,714.43                 39.70

                     Logons:                  0.44                  0.01

                   Executes:              6,434.11                148.98

               Transactions:                 43.19

  % Blocks changed per Read:    3.93    Recursive Call %:      9.04

 Rollback per transaction %:    4.01       Rows per Sort:     48.64

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

            Buffer Nowait %:   99.98       Redo NoWait %:     99.99

            Buffer  Hit   %:   99.60    In-memory Sort %:    100.00

            Library Hit   %:   97.61        Soft Parse %:     95.10

         Execute to Parse %:    1.47         Latch Hit %:     99.36

Parse CPU to Parse Elapsd %:   62.06     % Non-Parse CPU:     90.83

 Shared Pool Statistics        Begin   End

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

             Memory Usage %:  100.00  100.00

    % SQL with executions>1:   30.49   31.15

  % Memory for SQL w/exec>1:   34.11   34.20

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                                     % Total

Event                                               Waits    Time (s) Ela Time

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

db file sequential read                         5,023,080      75,306    41.99

CPU time                                                       74,925    41.77

global cache cr request                         4,851,421       6,662     3.71

db file scattered read                            181,344       3,582     2.00

latch free                                      4,553,880       3,572     1.99

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

从报告上看，CPU TIME在所有的事件中排在第二位，基本上和db file sequential read差不多。另外NONE PARSE CPU只有90%左右，说明PARSE消耗的CPU也不少。不过只有2/3天的时间来做这个工作，这部分优化工作就暂时不考虑了。从等待事件上看：

                                                                   Avg

                                                     Total Wait   wait    Waits

Event                               Waits   Timeouts   Time (s)   (ms)     /txn

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

db file sequential read         5,023,080          0     75,306     15     32.3

global cache cr request         4,851,421      4,394      6,662      1     31.2

db file scattered read            181,344          0      3,582     20      1.2

IO性能不算太好，不过也还算凑合，不会有太大的性能影响。这次优化我们重点放在索引优化上，因此需要考虑的其他方面的问题较少，只要确保索引调整后，不会有TOP SQL出现执行计划变坏就行了。基于这样的考虑，整个优化工作就变得很简单了，首先挑出需要优化的SQL，然后分析是否可以通过调整索引进行优化，如果可以就做出一个索引调整方案，由开发商实施就可以了。一些较小的索引调整可以立即进行，如果索引的大小超过200M，就需要中午或者晚上业务较小的时间进行。

我在Statspack报告中查找逻辑读较大的SQL，看了一下排在第一位的是一条SQL，一小时执行了50万次，每次开销为1000多个BUFFER GET，查了一下执行计划，是通过一个索引做范围扫描，看样子也没有多大的问题，只是执行频率较高而已。排在第二位的是一条很简单的SQL：

SELECT ORDER_ID ,

         NVL(PAYMENT_ID, -1) PAYMENT_ID,

         ACCT_ID,

         SERV_ID,

         ACTION,

         STATE ,

         TO_CHAR(CREATED_DATE,'YYYYMMDDHH24MISS'),

         TO_CHAR(STATE_DATE,'YYYYMMDDHH24MISS')   FROM A_XXX

         WHERE NVL(PAYMENT_ID,-1)>=:lPaymentID    AND STATE=:sState  ORDER BY

         NVL(PAYMENT_ID, -1), CREATED_DATE

这条SQL每次访问的BUFFER GET也只有不到2500，不过访问的次数也不少，有5、6万次。实际上这条SQL的写法是有问题的，我查了一下PAYMENT_ID字段上是有索引的，不过由于使用了NVL函数，导致了这个索引无法使用。实际上来说PAYMENT_ID是一个肯定大于0的字段，而在该字段上有一个>的过滤条件，因此满足该条件的记录PAYMEN_ID是肯定不为空的，所以如果把NVL函数去掉，改为：

SELECT ORDER_ID ,

         NVL(PAYMENT_ID, -1) PAYMENT_ID,

         ACCT_ID,

         SERV_ID,

         ACTION,

         STATE ,

         TO_CHAR(CREATED_DATE,'YYYYMMDDHH24MISS'),

         TO_CHAR(STATE_DATE,'YYYYMMDDHH24MISS')   FROM A_XXX

         WHERE PAYMENT_ID>=:lPaymentID    AND STATE=:sState  ORDER BY

         PAYMENT_ID, CREATED_DATE

从语义上来看，这两条SQL是完全等价的。因为PAYMENT_ID字段是十分常用的，因此PAYMENT_ID上的索引必须保留，因此这条SQL最佳的优化方案是修改程序，去掉PAYMENT_ID上的NVL函数。我和开发商沟通了一下，他们也认为这是开发人员的一个错误，这个NVL是可以去掉的，不过修改程序的审批和测试流程十分复杂，短期内很难实施，最好能有其他解决方案。如果要找其他解决方案，其他的方案就只能考虑在NVL(PAYMENT_ID,-1)上创建函数索引了。根据开发人员的介绍，这个SQL是查询PAYMENT_ID大于某个值的未处理过的记录的数量，STATE的值只有5/6个选择性一般。开发人员帮我找了一个PAYMENT_ID，我通过SELECT COUNT(*) FROM A_XXX WHERE PAYMENT_ID>:PID查了一下，满足大于PAYMENT_ID的记录大约占整个表的1/15。于是我再加上STATE的过滤条件进行查询，发现查出的记录综述占整个表的1/60。在这种情况下，创建NVL(PAYMENT_ID)+STATE的索引，效果比创建PAYMENT_ID的单列索引要好的多。A_XXX表的总记录数为200多万条，只要在业务不忙的时候，创建索引对系统影响不大。于是我建议中午的时候在这张表上创建这个索引。

中午的时候，开发商创建了这个复合索引。我吃完饭回到办公室是下午1:30，看了看系统的负载情况，CPU使用率只有50%左右。我知道这不可能是刚才那个索引的功劳，可能是中午很多营业厅都没什么业务。我做了一个STATSPACK的6几SNAP，生成了一份STATSPACK报告，看到这条SQL在半小时里执行了2万次不到，平均每次执行的buffer get的数量是1500左右，比刚才还是有明显的下降的，应该是执行计划出现了改变。为了确认这一点，我对这条SQL做了一个sprepsql，在SQL报告中，我明确的看到确实SQL执行的时候使用了新建的索引。

下午3点-5点是下午的业务高峰期。确实2点半之后，系统负载就在不断的上升，不过CPU使用率一直在80%左右徘徊。我心中难免有些喜悦，难道刚才那个索引居然能取得这么好的效果？

3点10分左右，CPU使用率再次突破了90%，几分钟后，达到了95%，3点30分左右，IDLE终于消失了。虽然我有点失望，不过这也在我的预料之中，想通过一两个索引达到优化的目的，是不太现实的。这套系统中，还有更多的工作需要做。