问题描述
数据库hang,大量latch: cache buffers chains异常等待事件 业务慢,主机CPU达到100%
Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
---|---|---|---|---|---|
latch: cache buffers chains | 644,380 | 321,720 | 499 | 47.19 | Concurrency |
log file sync | 1,324,320 | 173,152 | 131 | 25.40 | Commit |
DB CPU | 109,560 | 16.07 | |||
latch free | 20,281 | 4,206 | 207 | 0.62 | Other |
db file sequential read | 254,620 | 3,612 | 14 | 0.53 | User I/O |
执行慢的语句几乎都是一条undate语句,该语句执行走主键索引。该语句执行计划一直都没有变化,且为主键索引,性能一直较好。
5y22vy8jp06yz UPDATE service_deal_info SET server_info = :1, finishserv_time = to_char(systimestamp, 'yyyy-MM-dd HH24:MI:SS.ff3'), response_info = :2, restransaction_id = :3 WHERE orchestration_id = :4 AND business_id = :5
[10:25:48]----------------------------------------------------------------------------------------------
[10:25:48]| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
[10:25:48]----------------------------------------------------------------------------------------------
[10:25:48]| 0 | UPDATE STATEMENT | | | | 1 (100)|
[10:25:48]| 1 | UPDATE | SERVICE_DEAL_INFO | | | |
[10:25:48]| 2 | TABLE ACCESS BY INDEX ROWID| SERVICE_DEAL_INFO | 1 | 2367 | 0 (0)|
[10:25:48]| 3 | INDEX UNIQUE SCAN | PK_SERVICE_DEAL_INFO | 1 | | 0 (0)|
[10:25:48]----------------------------------------------------------------------------------------------
同时,发现此时还有一个delete操作,正在对改表做delete操作。初步判断改delete对其它undate有影响
3xccqv0u3f49x DELETE FROM SERVICE_DEAL_INFO WHERE 1=1 AND CALLSERV_TIME BETWEEN '2012-07-17 00:00:00.000' AND '2012-07-17 23:59:59.999'。
经过分析,比较了有无此delete时的数据库性能变化
无此删除操作时,平均逻辑读为15.47
Buffer Gets Executions Gets per Exec %Total Elap