首先看DB Time的时间,数据库是12核,正常上午上班4个小时CPU的消耗为500分钟,现在消耗为2,315.58 分钟,由经验推断系统大约有1个多小时不可用。
| Snap Id | Snap Time | Sessions | Cursors/Session |
---|---|---|---|---|
Begin Snap: | 19382 | 14-12月-12 08:00:08 | 389 | 17.0 |
End Snap: | 19386 | 14-12月-12 12:00:58 | 388 | 16.8 |
Elapsed: |
| 240.84 (mins) |
|
|
DB Time: |
| 2,315.58 (mins) |
|
|
然后查看数据库的等待事件,enq: TX - row lock contention,出现行锁的等待。
Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
---|---|---|---|---|---|
enq: TX - row lock contention | 35,932 | 105,380 | 2,933 | 75.8 | Application |
CPU time | 32,188 | 23.2 | |||
SQL*Net more data from client | 49,506 | 706 | 14 | .5 | Network |
log file sync | 61,036 | 310 | 5 | .2 | Commit |
log file parallel write | 61,243 | 289 | 5 | .2 | System I/O |
可以看到在修改一个表的时候耗时特别长,然而CPU Time非常少。可以肯定锁表了导致更新等待,与实施组确认,确实有一位新同事不太熟悉pl/sql,锁表了还不知道。最后跟实施组强调,在pl/sql中使用for update一定要加条件。
Elapsed Time (s) | CPU Time (s) | Executions | Elap per Exec (s) | % Total DB Time | SQL Id | SQL Module | SQL Text |
---|---|---|---|---|---|---|---|
82,479 | 2 | 37 | 2229.18 | 59.37 | g6f14msqa7nfx | UPDATE INTERRUPTION_NOTICE... | |
17,425 | 0 | 9 | 1936.13 | 12.54 | 83cgh64u3rj4q | UPDATE INTERRUPTION_NOTICE... | |
2,463 | 1 | 3 | 820.90 | 1.77 | 29sncq624rjjm | UPDATE INTERRUPTION_NOTICE... | |
2,439 | 0 | 8 | 304.91 | 1.76 | 2awhh62aphphx | UPDATE INTERRUPTION_APPLY_... | |
900 | 921 | 88 | 10.23 | 0.65 | b0sd24tx1zscm | SELECT * FROM(SELECT INNER_TAB... |