利用复合索引解决性能问题一例
故障发生时间：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 NORMALSQL> 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;剩下的老熊就讲的蛮仔细的了。
