FAST_START_MTTR_TARGET in REDO LOG TUNNING

This is a new feature introduced in Oracle 10g.

 

HOW REDO LOGS SIZE AFFECTS PERFORMANCE

------------------------------------------ 

The size of the redo log files can influence performance, because the behavior of

the database writer and archiver processes depend on the redo log sizes.

Generally, larger redo log files provide better performance, however

it must balanced out with the expected recovery time.Undersized log files

increase checkpoint activity and increase CPU usage.

Checkpoint frequency is affected by several factors, including log file size and

the setting of the FAST_START_MTTR_TARGET initialization parameter.

If the FAST_START_MTTR_TARGET parameter is set to limit the instance recovery time,

Oracle automatically tries to checkpoint as frequently as necessary.

Under this condition, the size of the log files should be large enough to avoid

additional checkpointing due to under sized log files.

Prior to 10g, it was not possible to provide a specific size recommendation for

redo log files, but redo log files in the range of a hundred megabytes to a few

gigabytes were considered reasonable.

As rule of thumb switching logs at most once every fifteen minutes.

The redo logfile sizing advisory is indeed a good feature in 10g wherein the redo logfile

size can tuned as per recommendation specified by column optimal_logfile_size of v$instance_recovery.

This feature requires setting the parameter "fast_start_mttr_target" for the advisory to take effect and

populate the column optimal_logfile_size.

You can try this with different settings of "FAST_START_MTTR_TARGET"

Also Remember-

If FAST_START_MTTR_TARGET is set to such a small value that it is impossible to

do a recovery within its time frame then target_mttr field of v$instance_recovery contains the effective

MTTR target which is larger than FAST_START_MTTR_TARGET.

If FAST_START_MTTR_TARGET is set to such a high value that even in worst case

entire buffer cache is dirty) recovery would not take that long, then

target_mttr field contains the estimated mttr in worst-case scenarios.

For more help on view "V$INSTANCE_RECOVERY" refer Note:180894.1 

Example:

--------

Enabling MTTR Advisory Enabling MTTR Advisory involves setting two parameters:

STATISTICS_LEVEL = TYPICAL
FAST_START_MTTR_TARGET > 0

SQL> show parameter fast_start

NAME                                         TYPE        VALUE

------------------------------------ ----------- ----------------------

fast_start_mttr_target               integer     71

<<< Here you can see that value of parameter "fast_start_mttr_target" = 71.

   This is best MTTR which the system can achieve for my system >>>>>>>   

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC     STATUS

----------          ----------       ----------          ----------    ----------      ---      -------

         1               1                485             10485760          1        NO  INACTIVE

         2               1                486             10485760          1       NO  INACTIVE

         3               1                487             10485760          1       NO   CURRENT

<<<<< Initially the size of redo logs are 10 MB  >>>>>>

SQL> select ACTUAL_REDO_BLKS,TARGET_REDO_BLKS,TARGET_MTTR,ESTIMATED_MTTR,

    OPTIMAL_LOGFILE_SIZE,CKPT_BLOCK_WRITES  from v$instance_recovery;

ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR OPTIMAL_LOGFILE_SIZE

----------------                     ----------------              -----------                --------------             --------------------

          942                           18432                           71                           59                            49     

The recommended optimal redolog file size is 49 MB as seen from column -OPTIMAL_LOGFILE_SIZE.

This is as per the setting of "fast_start_mttr_target" = 71.

SQL>  select ACTUAL_REDO_BLKS       ,TARGET_REDO_BLKS,TARGET_MTTR,ESTIMATED_MTTR,

     OPTIMAL_LOGFILE_SIZE,CKPT_BLOCK_WRITES       from v$instance_recovery;

ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR  OPTIMAL_LOGFILE_SIZE

---------------                           ----------------          -----------            --------------              --------------------

            597                                  18432                     71                       59                            49        

<<<< Here redo logs are re-created as per recommendations 49 MB >>>>>>

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

         4          1        490   51380224          1 NO  INACTIVE

         5          1        491   51380224          1 NO  ACTIVE

         6          1        492   51380224          1 NO  CURRENT

SQL> select ACTUAL_REDO_BLKS       ,TARGET_REDO_BLKS,TARGET_MTTR,ESTIMATED_MTTR,

  OPTIMAL_LOGFILE_SIZE,CKPT_BLOCK_WRITES       from v$instance_recovery;

ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR OPTIMAL_LOGFILE_SIZE

----------------                           ----------------          ----------                   --------------             --------------------

             113                             18432                     71                           58                                         49      

You can see that the "actual_redo_blks" column, that is current number of redo blocks

required to be read for recovery has reduced from 597 redo blocks earlier to 113

redo blocks once the log files are re-created with optimal settings.

This will speed up the Instance recovery time.

The MTTR target that your database can achieve given the current value of FAST_START_MTTR_TARGET is called the effective MTTR target. You can view your current effective MTTR by viewing the TARGET_MTTR column of the V$INSTANCE_RECOVERY view.

The default for fast_start_mttr_target is 300 seconds (5 Minutes). and maximum we can give 3600 ( 1 Hour).

Select * from V$MTTR_TARGET_ADVICE;

Whenever you set fast_start_mttr_target to a nonzero value, and while MTTR advisory is ON, Oracle Corporation recommends that you disable (set to 0) the following parameters:

 LOG_CHECKPOINT_TIMEOUT

 LOG_CHECKPOINT_INTERVAL

 FAST_START_IO_TARGET