I am getting the Checkpoint not complete error everday when the batch process is on .Hear you can see the arch stated at 10:09 and finshed at 10:26 almost 17min ..
Why does the arch takes such a long time .
Wed Feb 27 10:09:34 2002
ARC0: Beginning to archive log# 4 seq# 63533
ARC0: Completed archiving log# 4 seq# 63533
Wed Feb 27 10:09:43 2002
Thread 1 cannot allocate new log, sequence 63535
Checkpoint not complete
Current log# 5 seq# 63534 mem# 0: /oradata/SPRDPROD/log/redo05.log
Thread 1 advanced to log sequence 63535
Current log# 6 seq# 63535 mem# 0: /oradata/SPRDPROD/log/redo06.log
Wed Feb 27 10:09:45 2002
ARC0: Beginning to archive log# 5 seq# 63534
ARC0: Completed archiving log# 5 seq# 63534
Wed Feb 27 10:26:32 2002
logfile size 25M
Total 6 groups
what database version are using ,
Increase the number of archiver process(which oracle 8i should do automatically )
Increase the size of redo logs , but this may further deteriorate the problem if redo generation is fast and archiver is not keeping up the pace.
Increase the number of redo log groups , this should help.
going by your details , the log switch is tooooo fast, find out why.Reducing the size of redo generation would solve the problem.
Oracle8i Enterprise Edition Release 220.127.116.11.0 - Production
PL/SQL Release 18.104.22.168.0 - Production
CORE 22.214.171.124.0 Production
TNS for Solaris: Version 126.96.36.199.0 - Production
NLSRTL Version 188.8.131.52.0 - Production
This happens when batch job runs .massive writes ...
The main problem I think is the DBWR is not flusing the dirty buffer to the disk fast ?Once after completing the writes only the archival will come play ?
AM i correct ???
how often is log switch occuring ?
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
Check if u see relatively long time_waited for the “buffer busy wait” and “write complete wait” events in v$system_event.If yes , then DBWR is the culprit.
The solution to such a problem is either to compensate for the problem by making the checkpoint more aggressive, or to solve the problem by making the I/O more
Check out the parameter FAST_START_IO_TARGET Instead of performing large checkpoints at periodic intervals, the database writer tries to keep the number of dirty blocks in the buffer cache low enough to guarantee rapid recovery in the event of a crash.
Also since u mentioned it is a batch update , why not enable nologgin feature and do direct inserts and also drop index and recreate after batch update.This shall reduce redo generation. But be sure to take backup if u enable nologging.
EVENT TOTAL_WAITS TOTAL_TIMEOUTS
PX Idle Wait 68956 68228
latch free 86446 63216
LGWR wait for redo copy 109215 336
buffer busy waits 149287 4
control file sequential read 208153 0
PX Deq Credit: send blkd 255910 3083
file open 513925 0
db file parallel write 698350 0
control file parallel write 1489452 0
log file sequential read 1638493 0
direct path write 1915888 0
direct path read 5570534 0
log file sync 9887120 1515
log file parallel write 10563654 2
rdbms ipc message 26101401 4476717
db file scattered read 29051415 0
db file sequential read 97021769 0
u should monitor the waits during the batch writes.
you are forcing checkpoint every 5MB redo log and every 30 minutes so basically you have several checkpoint situations
every log switch
every 30 minutes
you should increase
if your batch job is very large you should consider increase redo log file size or add more groups plus above two parameters settings
you may also consider set FAST_START_IO_TARGET to 0 in order to disable checkpoints forced by this parameter if you dont have any kind of service level agreement with your customer (instance recovery with you redo log size arent very slow anyway)
[Edited by pando on 02-27-2002 at 03:38 PM]
Click Here to Expand Forum to Full Width