首先看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... |