利用复合索引解决性能问题一例
转帖自: 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 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语句,示例:
剩下的老熊就讲的蛮仔细的了。
向老熊致敬!