When setting the size of online redo log files, consider whether database will be in
mode. Online redo log files should be sized so that a filled group can
archived to a single unit of offline storage media (such as a tape or
disk), with the least amount of space on the medium left unused.
For example, suppose only one filled online redo log group can fit on a
tape and 49% of the tape's storage capacity remains unused.
In this case, it is better to decrease the size of the online redo log
files slightly, so that two log groups could be archived for
each tape. Also in 10g see OPTIMAL_LOGFILE_SIZE in V$INSTANCE_RECOVERY.
Optimal sizing of online redo logs is really important for every database administrator in order to achieve maximum performance . If your online redo log is not properly sized then it may result in frequent log switches, increases system waits and ties up the LGWR, ARCH and DBWR background processes and more disastrous situation can be where an instance crash makes you lose your entire data.
Oracle has been introducing one utility after the other to ease the life of database administrators. Redo Logfile Size Advisor is a great Oracle 10g advisory utility that helps you determine optimal online redo log file size. Once Redo Logfile Size Advisor determines the optimal online redo log file size then you just have to adjust the online redo log file size to the recommended optimal size thus avoiding the cumbersome task of manually determining optimal size for your online redo log files.
The ability of Oracle 10 g database to self-tune check-pointing can help us achieve good recovery times. You just have to specify a target time to complete the cache recovery phase of recovery with the FAST_START_MTTR_TARGET initialization parameter and Oracle will automatically vary the incremental checkpoint writes to meet that target. Remember your online redo log file size will be optimal if it does not drive incremental checkpoint processes more aggressively than is needed by the FAST_START_MTTR_TARGET initialization parameter.
Oracle Checkpoint Mechanism:
Oracle's checkpoint mechanism makes you ensured that the data contained in the buffer cache is safely written to disk on a regular basis. The two types of Oracle checkpoints are full and incremental.
More frequent log switches may result in decreased performance. If your redo logs switches so aggressively that the checkpoint associated with the log switch cannot get completed then CHECKPOINT NOT COMPLETE messages will be generated. Oracle will stop processing until the checkpoint completes successfully. Generally it is recommended to size your redo log file in a way that Oracle performs a log switch every 15 to 30 minutes.
A recommended approach is to
You can also check messages in the alert log in order to determine how fast Oracle is filling and switching logs. Suppose if your database redo log file size is set to 1MB. It means that Oracle switches the logs every 1 minute. So you will need to increase the size of redo log file to 30MB so that Oracle switches per 30 minutes.
It is also recommended to ensure that your online redo log files do not switch too often during high activity time. Instead in the period of high activity it should switch less while it should switch enough times during the time of low processing workloads. Many database administrators create PL/SQL programs to ensure that the logs switch every 15 to 30 minutes during times when activity is low.
Oracle ARCHIVE_LAG_TARGET can also be used to force a log switch after the specified amount of time elapses. The basic purpose of ARCHIVE_LAG_TARGET parameter is to control the amount of data that is lost and effectively increasing the availability of the standby database but many database administrators set ARCHIVE_LAG_TARGET parameter to make sure that the logs switch at regular intervals during lower activity time periods.
You should also keep in mind that how the size of the online redo log files will affect the instance recovery. Remember the lesser the checkpoints are taken; the longer will be the instance recovery duration. You can decrease the instance recovery time by appropriately setting the LOG_CHECKPOINT_TIMEOUT, LOG_CHECKPOINT_INTERVAL and FAST_START_MTTR_TARGET parameters.
Oracle V$INSTANCE_RECOVERY View:
We need to query the V$INSTANCE_RECOVERY view in order to use the Redo Logfile Size Advisor. Oracle V$INSTANCE_RECOVERY monitors the mechanisms available to users to limit recovery I/O. These include
Redo Logfile Size Advisor determines optimal online redo log file size based on the setting of the current FAST_START_MTTR_TARGET initialization parameter and MTTR statistics. Note that Redo Logfile Size Advisor will be enabled for your Oracle databases only if FAST_START_MTTR_TARGET is set.
Under-Sized Online Redo Log Files:
WRITES_LOGFILE_SIZE is the number of writes driven by the smallest redo log file size. WRITES_LOGFILE_SIZE drives the checkpoint process if your redo log file size is under sized. Consider a small redo log file where the value of FAST_START_MTTR_TARGET initialization parameter is set to 30. Performing a number of transactions generates some redo entries. Now we query the V$INSTANCE_RECOVERY view so as to capture the minimum optimal size of the redo log file.
SELECT TARGET_MTTR,ESTIMATED_MTTR,WRITES_MTTR,WRITES_LOGFILE_SIZE, OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;
TARGET_MTTR ESTIMATED_MTTR WRITES_MTTR WRITES_LOGFILE_SIZE OPTIMAL_LOGFILE_SIZE
The results show that the checkpoint process is driven by WRITES_LOGFILE_SIZE and the optimal log file size is at least 32MB.
The value for optimal_logfile_size is expressed in MB. This value changes frequently based on the DML load on your database. Now we will recreate the log file with recommended optimal log file size. Running the above query again shows that now our checkpoint process is driven by the WRITES_MTTR that is the number of writes driven by the FAST_START_MTTR_TARGET parameter
SELECT TARGET_MTTR,ESTIMATED_MTTR,WRITES_MTTR,WRITES_LOGFILE_SIZE, OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;TARGET_MTTR ESTIMATED_MTTR WRITES_MTTR WRITES_LOGFILE_SIZE OPTIMAL_LOGFILE_SIZE
---------------------- ----------------------------- ---------------------- ----------------------------------- -------------------------------------
34 38 934 0
DBA's Home Page - A website for Oracle, Greenplum and PostgreSQL DBA's > DBA's Forum, Routine Tasks and Activities >