log file switch (checkpoint incomplete)的問題定位，incompletezipfile

log file switch (checkpoint incomplete)的問題定位，incompletezipfile

      今天測試環境下應用慢，探索資料庫出了問題，直接上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

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。