Oracle Database Discussions

Regards

I have a specific question regarding logfile size. I have deployed a test database and i was exploring certain aspects with regards to selecting optimal size of redo logs for performance tuning using optimal_logfile_size view from v$instance_recovery. My main goal is to reduce the redo bytes required for instance recovery. Currently i have not been able to optimize the redo log file size. Here are the steps i followed:-

In order to use the advisory from v$instance_recovery i had to set fast_start_mttr_target parameter which is by default not set so i did these steps:-

1)SQL> sho parameter fast_start_mttr_target;

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
fast_start_mttr_target integer 0

2) Setting the fast_start_mttr_target requires nullifying following deferred parameters :-
SQL> show parameter log_checkpoint;

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE

SQL> select ISSES_MODIFIABLE,ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE,ISMODIFIED from v$parameter where name like'log_checkpoint_timeout';

ISSES_MODIFIABL ISSYS_MODIFIABLE ISINSTANCE_MODI ISMODIFIED
--------------- --------------------------- --------------- ------------------------------
FALSE IMMEDIATE TRUE FALSE

SQL> alter system set log_checkpoint_timeout=0 scope=both;

System altered.

SQL> show parameter log_checkpoint_timeout;

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
log_checkpoint_timeout integer 0

3) Now setting fast_start_mttr_target

SQL> select ISSES_MODIFIABLE,ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE,ISMODIFIED from v$parameter where name like'fast_start_mttr_target';

ISSES_MODIFIABL ISSYS_MODIFIABLE ISINSTANCE_MODI ISMODIFIED
--------------- --------------------------- --------------- ------------------------------
FALSE IMMEDIATE TRUE FALSE

Setting the fast_mttr_target to 1200 = 20 minutes of checkpoint switching according to Oracle recommendation

Querying the v$instance_recovery view

4) 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 CKPT_BLOCK_WRITES
---------------- ---------------- ----------- -------------- -------------------- -----------------
276 165888 *93* 59 361 16040

Here Target Mttr was 93 so i set the fast_mttr_target to 120

SQL> alter system set fast_start_mttr_target=120 scope=both;

System altered.

Now the logfile size suggested by v$instance_recovery is 290 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 CKPT_BLOCK_WRITES
---------------- ---------------- ----------- -------------- -------------------- -----------------
59 165888 93 59 290 16080

After altering the logfile size to 290 as show below by v$log view :-

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES from v$log;

GROUP# THREAD# SEQUENCE# BYTES
---------- ---------- ---------- ----------
1 1 24 304087040
2 1 0 304087040
3 1 0 304087040
4 1 0 304087040
5 ) After altering the size i have observed the anomaly as redo log blocks to be applied for recovery has increased from *59 to 696* also now v$instance_recovery view is now suggesting the logfile size of *276 mb*. Have i misunderstood something

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 CKPT_BLOCK_WRITES
---------------- ---------------- ----------- -------------- -------------------- -----------------
*696* 646947 120 59 *276* 18474

Please clarify the above output i am unable to optimize the logfile size and have not been able to achieve the goal of reducing the redo log blocks to be applied for recovery, any help is appreciated in this regard.