Fix frequent log switches

Question:  I have a batch job that runs nightly, and it suddenly starting taking 10x time to execute.  Log file switch (checkpoint incomplete)”.   I also see frequent log switches.  Here is the output of a "ls -al", showing frequent log switches:

As you see, as of 5:00 PM the log were switching every 10 minutes.  At 20:16 there is a pause in the redo logs, followed by a blast of archived redo logs at large sizes.  How do I correct the "log file switch checkpoint incomplete error?

Answer:  During your batch updates you should see high process activity from DBWR, LGWR and ARCH background processes (using the "ps -ef " command.).  You can also repeat the "ls -alt|hread" commands in the redo log directory and watch ARCH write the redo log files.

•   Also be aware of the v$log_history view for showing the frequency of log switches.

• Here are the notes for the log file switch incomplete error message.

In general, you should size your online redo logs such that they switch no more frequently than 4 times per hour at peak DML levels (the busiest update times).  In sum, the solution is to try these fixes (in order):

•  Increase the size of the online redo logs.  In this example, a redo log switch every 10 minutes should be doubled, and change the redo log file size from 1 gigabytes to two gigabytes.  His should reduce the work on the checkpoint process.

•  Increase the number of redo log groups.  More redo log groups can improve concurrent throughput. 

• Change the updates to run in NOLOGGING.  This will not work immediately, but it will prevent future problems. Beware of the danger of improper usage of the nologging option.

In some cases, this may also help improve the performance of databases with too frequent redo log activity:

• Add additional log writer processes (LGWR).
  
• Ensure that the archived redo log filesystem resides on a separate physical disk spindle.
  
• Put the archived redo log filesystem on super-fast solid-state disks.
  
• If you also see high buffer busy waits, consider increasing the number of freelists for the target tables and indexes.  You can also use bitmap freelists (segment space management auto).
  
• For inserts, consider using the APPEND hint.
• See the notes for insert tuning.
  

Guarantee your Success! Oracle is the world's most complex, robust and flexible database, considered impossible to master without a mentor. That's why all BC Oracle trainers are working professionals, experts in Oracle who share their tips and secrets.

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.                      Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services   Copyright © 1996 -  2012  All rights reserved. Oracle © is the registered trademark of Oracle Corporation.