今天測试环境下应用慢。发现数据库出了问题，直接上AWR报告。因为是虚拟机。所以不用贴cpu的个数，能够发现负载高。
|
Snap Id |
Snap Time | Sessions | Cursors/Session | |
|---|---|---|---|---|
| Begin Snap: | 15257 | 30-Jun-15 09:30:57 | 558 | 5.3 |
| End Snap: | 15258 | 30-Jun-15 10:00:27 | 582 | 5.7 |
| Elapsed: | 29.50 (mins) | |||
| DB Time: | 717.00 (mins) |
查看等待时间，发现日志切换在等待。
Top 10 Foreground Events by Total Wait Time
| Event | Waits | Total Wait Time (sec) | Wait Avg(ms) | % DB time | Wait Class |
|---|---|---|---|---|---|
| log file switch (checkpoint incomplete) | 350 | 11.3K | 32229 | 26.2 | Configuration |
| db file sequential read | 569,141 | 8433.8 | 15 | 19.6 | User I/O |
| read by other session | 1,228,260 | 6279.9 | 5 | 14.6 | User I/O |
| buffer busy waits | 452,194 | 6138 | 14 | 14.3 | Concurrency |
| DB CPU | 3121.5 | 7.3 | |||
| enq: TX - row lock contention | 300 | 1934.5 | 6448 | 4.5 | Application |
| direct path read | 45,561 | 1647.4 | 36 | 3.8 | User I/O |
| db file scattered read | 89,177 | 1617.5 | 18 | 3.8 | User I/O |
| db file parallel read | 29,761 | 1079.4 | 36 | 2.5 | User I/O |
| log file sync | 9,864 | 720.7 | 73 | 1.7 | Commit |
半小时切换了23次，redo日志我看了一下，一个为512M。
| Statistic | Total | per Hour |
|---|---|---|
| log switches (derived) | 23 | 46.78 |
最直接的方法是看下数据块修改的情况，再去查SQL。一眼看去就是物化视图MV_CONTRACT_INFO导致，70,211,408是修改数据库的数量。换算成数据量是70211408*8/1024/1024=535.6G。只是这个是最大的redo。事实上真实的比这个小，即使小，也非常可观。
非常明显。是有人在刷新物化视图，通知开发不要在上班时间内刷新物化视图。
Segments by DB Blocks Changes
- % of Capture shows % of DB Block Changes for each top segment compared
- with total DB Block Changes for all segments captured by the Snapshot
| Owner | Tablespace Name | Object Name | Subobject Name | Obj. Type | DB Block Changes | % of Capture |
|---|---|---|---|---|---|---|
| LCAM_ZC_0130 | WZ | MV_CONTRACT_INFO | TABLE | 70,211,408 | 99.91 | |
| LCAM_SC | SPROC4GD_DATA | GCP_D_S_ALL | TABLE | 34,864 | 0.05 | |
| LCAM_PUB_CS | LCAM_SYS_TBS | SYS_LOB0001127099C00014$$ | LOB | 5,104 | 0.01 | |
| LCAM_PUB_15630 | WZ | SOA_SERVICE_LOAD_RESULT | TABLE | 5,024 | 0.01 | |
| LCAM_PUB_CS | LCAM_SYS_TBS | PK_LOAD_ID | INDEX | 4,752 | 0.01 |