RE: archivelog performance issues

From: Richard Croasmun <>
Date: Tue, 7 Jul 2009 17:23:43 +0000
Message-ID: <BAY117-W34CE0A5A102BA3E2AAF2419B280_at_phx.gbl>

On Tue, 7 Jul 2009 12:31:43 -0400 wrote:  

     Some things to check before you switch to archive log mode:  

  • How frequently are you seeing log switches, at peak load time?
                 Size your online redo so that you get a switch about every 15-30 minutes, at peak.
                  If your load variaes greatly from peak to non-peak, you may want to use
                 archive_lag_target to get more frequent switches during off-peak times.
-                    Make sure you have at least three redo log groups.

I agree 100% with those that state log archive mode should be based on recoverability, not performance. In addition, from a performance point of view, it seems to me that the only additional overhead is a copy of the online redo log to an archive log for each log switch. I would say that all the points about log size, frequency of log switches, number of online logs etc would apply even when the DB is NOT in archive log mode. The only exception would be having enough online logs (or large enough logs) such that the copy of a particular log finishes before the database wants to use that online redo log again. Obviously, if the DB is in archive log mode, the overhead of the copy on the DB could be reduced by placing the archive logs on separate disks from the database and online logs to minimize I/O contention. An last, but not least, don't let the archive log area fill up!! ;-) Am I missing anything here?  


Received on Tue Jul 07 2009 - 12:23:43 CDT

Original text of this message