故障发生时间:12月6日早上
系统环境:HP Superdome系列,128G内存,64CPU,Oracle 9.2.0.8
故障现象:CPU占用将近100%,运行队列达到60-80,应用反应速度很慢。

这是一个省级电信的核心系统。

在用户反映速度很慢后,在主机上检查发现CPU很高,将近100%,而运行队列达到了60-80。检查Oracle,发现很多的会话在等待latch free,latch#为98

SQL> select * from v$latchname where latch#=98;

    LATCH# NAME
---------- ----------------------------------------------------------------
        98 cache buffers chains

检查正在等待latch free的会话正在执行的SQL,大部分都在执行类似于下面的SQL:

SELECT SUM(cnt),
       to_char(nvl(SUM(nvl(amount, 0)) / 100, 0), 'FM9999999999990.90') amount
  FROM (select count(payment_id) cnt, SUM(amount) amount
          from payment
         where staff_id = 592965
           and CREATED_DATE >= trunc(sysdate)
           and state = 'C0C'
           and operation_type in ('5KA', '5KB', '5KC', '5KP'))

看起来这个SQL并不复杂,查看其执行计划:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             |  Name                      | Rows  | Bytes | Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                            |     1 |    26 |   125K|       |       |
|   1 |  SORT AGGREGATE                       |                            |     1 |    26 |       |       |       |
|   2 |   VIEW                                |                            |     1 |    26 |   125K|       |       |
|   3 |    SORT AGGREGATE                     |                            |     1 |    30 |       |       |       |
|*  4 |     TABLE ACCESS BY GLOBAL INDEX ROWID| PAYMENT                    | 19675 |   576K|   125K| ROWID | ROW L |
|*  5 |      INDEX RANGE SCAN                 | IDX_PAYMENT_CREATED_DATE8  |  1062K|       |  3919 |       |       |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("PAYMENT"."STAFF_ID"=521840 AND "PAYMENT"."STATE"='C0C' AND ("PAYMENT"."OPERATION_TYPE"='5KA' OR
              "PAYMENT"."OPERATION_TYPE"='5KB' OR "PAYMENT"."OPERATION_TYPE"='5KC' OR "PAYMENT"."OPERATION_TYPE"='5KP'))
   5 - access("PAYMENT"."CREATED_DATE">=TRUNC(SYSDATE@!))

Note: cpu costing is off

从执行计划里面可以看到,Oracle评估出,利用索引扫描返回的行数高达100万行,可想而知,这就是产生众多latch buffers chains latch争用的原因。
检查PAYMENT表的索引:

SQL> select index_name,index_type from dba_indexes where table_name='PAYMENT' and table_owner='ACCT';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
IDX_OPERATED_PAYMENT_SERIAL8   NORMAL
IDX_PAYMENT_ACCT_ID8           NORMAL
IDX_PAYMENT_CREATED_DATE8      NORMAL
IDX_PAYMENT_PAYED_METHOD8      NORMAL
IDX_PAYMENT_PAYMENT_METHOD8    NORMAL
IDX_PAYMENT_SERV_ID8           NORMAL
IDX_PAYMENT_STAFF_DATE8        NORMAL
IDX_PAYMENT_STATE_DATE8        NORMAL
PK_PAYMENT13                   NORMAL

SQL> select index_name,column_name,column_position from dba_ind_columns where table_owner='ACCT' and table_name='PAYMENT' order by 1,3;

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
IDX_OPERATED_PAYMENT_SERIAL8   OPERATED_PAYMENT_SERIAL_NBR                  1
IDX_PAYMENT_ACCT_ID8           ACCT_ID                                      1
IDX_PAYMENT_CREATED_DATE8      CREATED_DATE                                 1
IDX_PAYMENT_PAYED_METHOD8      PAYED_METHOD                                 1
IDX_PAYMENT_PAYMENT_METHOD8    PAYMENT_METHOD                               1
IDX_PAYMENT_SERV_ID8           SERV_ID                                      1
IDX_PAYMENT_STAFF_DATE8        STAFF_ID                                     1
IDX_PAYMENT_STAFF_DATE8        STATE_DATE                                   2
PK_PAYMENT13                   PAYMENT_ID                                   1

可以看到执行计划中的使用的索引IDX_PAYMENT_CREATED_DATE8是在CREATED_DATE列上建立的单列索引。

这个SQL在之前却没有出现这个问题,那问题在哪里?
如果了解电信系统的人,会知道在出帐后会有一个批量销帐的动作,这导致在这个特殊的时间里,用CREATED_DATE>=TRUNCATE(SYSDATE)这个条件会从索引扫描中返回大量的行。而实际上而回表之后用其他条件过滤后的行数仅约2万行(这是评估的数据,实际的数据远远比这个少)。很显然,如果我们建立一个复合索引,那么索引扫描返回的行数将大大减少。这里STAFF_ID这个字段是与CREATED_DATE建立复合索引最好的字段。

在STAFF_ID和CREATE_DATE列上建立复合索引后,系统马上恢复正常。不过这里把STAFF_ID做为复合索引的前导列,有两个方面的考虑,一个是根据这个SQL来看,这会大大减少索引叶块的逻辑读数量;二是会减少索引叶块的热点竞争(CREATE_DATE是单向增长的字段)。至于深层次的分析,则要结合应用,必竟做为故障处理,首要的目标是解决当前的问题。

总结:
利用合适的复合索引,能够有效地减少索引扫描返回的行数,提高性能
熟悉应用系统的业务,可以更清楚地知道问题的根源,减少故障处理时间

,
Trackback

8 comments untill now

  1. 复合索引增大选择性。好文!

  2. 小丑鱼 @ 2008-12-18 09:06

    我也刚遇到你这种情况 谢谢了

  3. 时间字段上有统计信息是很危险的!oltp系统上,如果时间的字段的distinct value很高,很容易导致oracle判断错误,走到时间字段上的

    老熊 Reply:

    @stronghearted, 或许不同的系统有不同的情况,只能根据业务系统来使用相应的统计信息收集策略了。

  4. 不过这里把STAFF_ID做为复合索引的前导列,有两个方面的考虑,一个是根据这个SQL来看,这会大大减少索引叶块的逻辑读数量;二是会减少索引叶块的热点竞争(CREATE_DATE是单向增长的字段)

    老熊,这2个是怎么看出来的?

  5. 老熊大师, 像这种SQL的话,如果增加了复合索引,是不是可以考虑把
    CREATED_DATE上面的单独索引去掉。

    老熊 Reply:

    @豆豆, 对于这条SQL来说,CREATED_DATE单列索引就没多少作用了,但索引往往不是只针对,或者说是适用于一条SQL的啊。

  6. 二是会减少索引叶块的热点竞争(CREATE_DATE是单向增长的字段)

    这一点是业务逻辑的原因,创建时间是递增。

Add your comment now