Checkpoint : When LGWR (Log Writer) need to switch next log file, it asks DBWR(DB Writer) to flash dirty (Updated) block into the disk this is checkpoint.
log file switch (checkpoint incomplete): Oracle can't reuse a log file if all modified blocks are not flashed onto the disk, it will wait until flash modified blocks by the previous checkpoint.
Cause : If there are few/ too small redo logs and system produces enough redo to cycle through all the logs before DBWR has been able to complete the checkpoint, then increase the size or number of redo logs.
If DBWR performance slow due to System I/O.
Solution : If few/small redo logs increase the redo logs, analyze DBWR performance In my case redo was small size.
SQL> SET LINE 200
SQL> col INSTANCE_NAME FORMAT A10
SQL> col SESSION_INFO FORMAT A15
SQL> col EVENT FORMAT A40
SQL> col WAIT_CLASS FORMAT A20
SQL> col WAIT_TIME FORMAT 99999999
SQL>
SQL> SELECT I.INSTANCE_NAME, W.SID||':'||S.SERIAL# SESSION_INFO, W.EVENT, W.WAIT_CLASS_ID, W.WAIT_CLASS, W.WAIT_TIME
2 FROM GV$SESSION_WAIT W, GV$SESSION S, GV$SQL Q, GV$INSTANCE I
3 WHERE W.INST_ID=S.INST_ID
4 AND S.INST_ID=Q.INST_ID
5 AND I.INST_ID=Q.INST_ID
6 AND W.SID=S.SID
7 AND S.SQL_ID=Q.SQL_ID
8 AND W.WAIT_TIME =0
AND W.EVENT<>'SQL*Net message from client'
AND W.WAIT_CLASS<>'Idle'
9 10 11 ORDER BY W.SID;
INSTANCE_N SESSION_INFO EVENT WAIT_CLASS_ID WAIT_CLASS WAIT_TIME
---------- --------------- ---------------------------------------- ------------- -------------------- ---------
spftldb 3408:1595 log file switch (checkpoint incomplete) 3290255840 Configuration 0
spftldb 5666:895 log file switch (checkpoint incomplete) 3290255840 Configuration 0
spftldb 5666:895 log file switch (checkpoint incomplete) 3290255840 Configuration 0
spftldb 5666:895 log file switch (checkpoint incomplete) 3290255840 Configuration 0
SQL>
log file switch (checkpoint incomplete): Oracle can't reuse a log file if all modified blocks are not flashed onto the disk, it will wait until flash modified blocks by the previous checkpoint.
Cause : If there are few/ too small redo logs and system produces enough redo to cycle through all the logs before DBWR has been able to complete the checkpoint, then increase the size or number of redo logs.
If DBWR performance slow due to System I/O.
Solution : If few/small redo logs increase the redo logs, analyze DBWR performance In my case redo was small size.
SQL> SET LINE 200
SQL> col INSTANCE_NAME FORMAT A10
SQL> col SESSION_INFO FORMAT A15
SQL> col EVENT FORMAT A40
SQL> col WAIT_CLASS FORMAT A20
SQL> col WAIT_TIME FORMAT 99999999
SQL>
SQL> SELECT I.INSTANCE_NAME, W.SID||':'||S.SERIAL# SESSION_INFO, W.EVENT, W.WAIT_CLASS_ID, W.WAIT_CLASS, W.WAIT_TIME
2 FROM GV$SESSION_WAIT W, GV$SESSION S, GV$SQL Q, GV$INSTANCE I
3 WHERE W.INST_ID=S.INST_ID
4 AND S.INST_ID=Q.INST_ID
5 AND I.INST_ID=Q.INST_ID
6 AND W.SID=S.SID
7 AND S.SQL_ID=Q.SQL_ID
8 AND W.WAIT_TIME =0
AND W.EVENT<>'SQL*Net message from client'
AND W.WAIT_CLASS<>'Idle'
9 10 11 ORDER BY W.SID;
INSTANCE_N SESSION_INFO EVENT WAIT_CLASS_ID WAIT_CLASS WAIT_TIME
---------- --------------- ---------------------------------------- ------------- -------------------- ---------
spftldb 3408:1595 log file switch (checkpoint incomplete) 3290255840 Configuration 0
spftldb 5666:895 log file switch (checkpoint incomplete) 3290255840 Configuration 0
spftldb 5666:895 log file switch (checkpoint incomplete) 3290255840 Configuration 0
spftldb 5666:895 log file switch (checkpoint incomplete) 3290255840 Configuration 0
SQL>
No comments:
Post a Comment