Checkpoint

[Subha]

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

Datatbase details
log_checkpoint_interval 10000
log_checkpoint_timeout 1800
log_checkpoints_to_alert FALSE
log_buffer 262144
logfile size 25M
Total 6 groups

[gpsingh]

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.

[Subha]

Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
PL/SQL Release 8.1.6.3.0 - Production
CORE 8.1.6.0.0 Production
TNS for Solaris: Version 8.1.6.3.0 - Production
NLSRTL Version 3.4.0.0.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 ???

[Sureshy]

how often is log switch occuring ?

[gpsingh]

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

[Subha]

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

[gpsingh]

u should monitor the waits during the batch writes.

[pando]

log_checkpoint_interval 10000
log_checkpoint_timeout 1800


you are forcing checkpoint every 5MB redo log and every 30 minutes so basically you have several checkpoint situations

every log switch
every 5MB
every 30 minutes

you should increase

log_checkpoint_interval 51200
log_checkpoint_timeout 0

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]