Checkpoint
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Checkpoint

  1. #1
    Join Date
    Feb 2001
    Posts
    119
    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

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    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.


  3. #3
    Join Date
    Feb 2001
    Posts
    119
    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 ???

  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    how often is log switch occuring ?
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  5. #5
    Join Date
    Feb 2001
    Posts
    389
    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.

  6. #6
    Join Date
    Feb 2001
    Posts
    119
    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


  7. #7
    Join Date
    Feb 2001
    Posts
    389
    u should monitor the waits during the batch writes.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width