Logfileswitch (checkpointincomplete) problem locating

Logfileswitch (checkpointincomplete) problem locating

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)

Today, in the test environment, the application is slow and an error occurs in the database. Report the error directly to AWR. Because it is a virtual machine, you do not need to paste the number of CPUs, you can find that the load is high.



Check the wait time and find that the log switch is waiting.


Top 10 Foreground Events by Total Wait Time

Event	Waits	Total Wait Time (sec)	MS (Wait Avg)	% DB time	Wait Class
Log file switch (checkpoint incomplete)	350	11.3 K	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

Switching 23 times in half an hour, I checked the redo log, one of which is 512 MB.

Statistic	Total	Per Hour
Log switches (derived)	23	46.78

The most direct method is to check the data block changes and then check the SQL. At a glance, it is caused by the materialized view MV_CONTRACT_INFO. 70,211,408 is the number of databases changed, the converted data volume is 70211408*8/1024/1024 = 535.6 GB, but this is the largest redo. Actually, it is smaller than this, even if it is small, it is very impressive. Obviously, some people are refreshing the materialized view, notifying developers not to refresh the materialized view during working hours.