利用复合索引解决性能问题一例

利用复合索引解决性能问题一例

转帖自： http://www.laoxiong.net/composite_index_overcome_performance_problem.html

 

老熊的博客(http://www.laoxiong.net)

 

Oracle性能优化 12月 8th, 2008

故障发生时间：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是单向增长的字段）。至于 深层次的分析，则要结合应用，必竟做为故障处理，首要的目标是解决当前的问题。

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

 

 

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

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

老熊水平高，许多基础知识没说。现整理一下老熊解决问题的基本流程：

1.会话的等待事件可以通过v$session_wait来查找，然后通过v$session和v$sql即可找到相对应的sql语句，示例：

select u.sid, substr(u.username,1,12) user_name, s.sql_text from v$sql s, v$session u where s.hash_value = u.sql_hash_value and sql_text not like '%from v$sql s, v$session u%' order by u.sid;

 剩下的老熊就讲的蛮仔细的了。

向老熊致敬！