PURPOSE
-------

To introduce REDO LOGS SIZING ADVISORY.
 
SCOPE & APPLICATION
-------------------

DBA's and Support Analyst's.


Redo Log Tuning Advisory
-----------------------------

This is a new feature introduced in Oracle 10g.

NOTE:
=====
The feature is available in the Enterprise Edition only.
Please refer to 
Note 951835.1 - ORA-02097 and ORA-00439 When Setting FAST_START_MTTR_TARGET To Value 300


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  

You can also obtain redo sizing advice on the Redo Log Groups page of 
Oracle Enterprise Manager Database Control.


Example:
--------

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,
  2   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,
  2  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.

RELATED DOCUMENTS
-----------------
Oracle 10g Performance Tuning Guide.