Determining if Online REDO Logs are Sized Properly

posted Aug 3, 2011, 5:39 AM by Sachchida Ojha   [ updated Aug 3, 2011, 5:49 AM ]

When setting the size of online redo log files, consider whether database will be in ARCHIVELOG mode. Online redo log files should be sized so that a filled group can be 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.

With multiplexed groups of online redo logs, all members of the same group must be the same size. Members of different groups can have different sizes. However, there is no advantage in varying file size between groups. If checkpoints are not set to occur between log switches, make all groups the same size to guarantee that checkpoints occur at regular intervals.

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.

  1. Oracle Full Checkpoint Mechanism ensures that all of the dirty blocks contained in the buffer cache are written to the data files and the database is thought of as being in a synchronized state at this time.
  2. Oracle Incremental Checkpoint Mechanism does write blocks that have been dirty the longest time to disk. It does not ensure that all of the dirty blocks contained in the cache are written to the data files.

Log Switches:

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

  1. Query V$LOG view to determine the current size of the redo log members.
  2. Record the number of log switches per hour.
  3. Increase the log file size so that Oracle switches at the recommended rate of one switch per 15 to 30 minutes.

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

  • Setting LOG_CHECKPOINT_TIMEOUT parameter.
  • Setting LOG_CHECKPOINT_INTERVAL parameter.
  • Setting FAST_START_MTTR_TARGET parameter.
  • Setting the size of the smallest redo log.

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
----------------------     -----------------------------    ----------------------       -----------------------------------      -------------------------------------
            30                            28                                          0                                     1022                                                32

The results show that the checkpoint process is driven by WRITES_LOGFILE_SIZE and the optimal log file size is at least 32MB.

Adjusting Redo Log Files to Optimal Log File Size:

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                
Comments