今天测试环境下应用慢,发现数据库出了问题,直接上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,一眼看去就是物化视图gg_C_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 |
---|---|---|---|---|---|---|
***** | gg | gg_C_INFO | TABLE | 70,211,408 | 99.91 | |
***) | gg | gg_D_S | TABLE | 34,864 | 0.05 | |
** | gg_TBS | SYS_LOB0001127099C00014$$ | LOB | 5,104 | 0.01 | |
** | gg | ggLOAD_RESULT | TABLE | 5,024 | 0.01 | |
** | **_TBS | PK_LOAD_ID | INDEX | 4,752 | 0.01 |