Locate the problem of log file switch (checkpoint incomplete), incompletezipfile

Locate the problem of log file switch (checkpoint incomplete), incompletezipfile

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.

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)

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.
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

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.