今天测试环境下应用慢，发现数据库出了问题，直接上AWR报告。
由于是虚拟机，所以不用贴cpu的个数，可以发现负载高。
查看等待时间，发现日志切换在等待。
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，其实真实的比这个小，即使小，也非常可观。很明显，是有人在刷新物化视图，通知开发不要在上班时间内刷新物化视图。
|