Oracle MTTR advisory or FAST_START_MTTR_TARGET

It reduces the time required for cache recovery, after shutdown abort or Oracle crashed


Disable MTTR advisory

FAST_START_MTTR_TARGET = 0
LOG_CHECKPOINT_INTERVAL = 200000



Enabling MTTR Advisory

FAST_START_MTTR_TARGET to a value greater than zero enables the fast-start checkpointing feature.


STATISTICS_LEVEL = TYPICAL
FAST_START_MTTR_TARGET > 0 should be more then zero ....


FAST_START_MTTR_TARGET initialization parameter to 300. To modify FAST_START_MTTR_TARGET use: "

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



apt-rdbms-01.aptus.co.uk:APTDB\sys> SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;

OPTIMAL_LOGFILE_SIZE
--------------------
<<<----- MTTR advisory is missing






apt-rdbms-01\sys> !cat mttr.sql

set linesize 145
set pagesize 1000
set trimout on
set trimspool on
Set Feedback off
set timing off
set verify off


prompt

prompt -- ----------------------------------------------------------------------- ---

prompt -- MTTR target ---

prompt -- ----------------------------------------------------------------------- ---


set heading off
select ' fast_start_mttr_target '||value ||' (seconds)' from V$PARAMETER where name='fast_start_mttr_target'
union
select ' statistics_level '||value from V$PARAMETER where name='statistics_level' ;

select ' '||Name||Lpad(To_char(value),48-length(Name))|| ' (Default : '||Isdefault||')' from V$PARAMETER where name like 'log_checkpoint_%'




rdbms-01:DB\sys> @mttr
fast_start_mttr_target 0 (seconds)
statistics_level TYPICAL
log_checkpoint_interval 0 (Default : TRUE)
log_checkpoint_timeout 1800 (Default : TRUE)
log_checkpoints_to_alert FALSE (Default : TRUE)



rdbms-01:DB\sys>!cat mttr1.sql

column RECOVERY_ESTIMATED_IOS format 999999999 heading "Recovery Estim. IOS"
column ACTUAL_REDO_BLKS format 999999999 heading "Actual|Redo Blk."
column TARGET_REDO_BLKS format 999999999 heading "Target|Redo Blk."
column LOG_FILE_SIZE_REDO_BLKS format 999999999 heading "Log file size|Redo Blk."
column LOG_CHKPT_TIMEOUT_REDO_BLKS format 999999999 heading "Log Chk. Timeout|Redo Blk."
column LOG_CHKPT_INTERVAL_REDO_BLKS format 999999999 heading "Log Chk. Interval|Redo Blk."
column FAST_START_IO_TARGET_REDO_BLKS format 999999999 heading "Fast Start IO Target|Redo Blk."
column TARGET_MTTR format 999999999 heading "Target|MTTR"
column ESTIMATED_MTTR format 999999999 heading "Estimated|MTTR"
column CKPT_BLOCK_WRITES format 999999999 heading "Chk. Block Writes"
column OPTIMAL_LOGFILE_SIZE format 999999999 heading "Optimal Logfile Size"
column ESTD_CLUSTER_AVAILABLE_TIME format 999999999 heading "Estim. Cluster|Available Time"
column WRITES_MTTR format 999999999 heading "Writes MTTR"
column WRITES_LOGFILE_SIZE format 999999999 heading "Writes Logfile Size"
column WRITES_LOG_CHECKPOINT_SETTINGS format 999999999 heading "Writes Log Chk Set."
column WRITES_OTHER_SETTINGS format 999999999 heading "Writes Other Set."
column WRITES_AUTOTUNE format 999999999 heading "Writes Autotune"
column WRITES_FULL_THREAD_CKPT format 999999999 heading "Writes Full Thread Ckpt"


Select
RECOVERY_ESTIMATED_IOS
, ACTUAL_REDO_BLKS
, TARGET_REDO_BLKS
, LOG_FILE_SIZE_REDO_BLKS
, LOG_CHKPT_TIMEOUT_REDO_BLKS
, LOG_CHKPT_INTERVAL_REDO_BLKS
From
V$INSTANCE_RECOVERY
;

Select
FAST_START_IO_TARGET_REDO_BLKS
, TARGET_MTTR
, ESTIMATED_MTTR
, CKPT_BLOCK_WRITES
, OPTIMAL_LOGFILE_SIZE
, ESTD_CLUSTER_AVAILABLE_TIME
From
V$INSTANCE_RECOVERY ;

Select
WRITES_MTTR
, WRITES_LOGFILE_SIZE
, WRITES_LOG_CHECKPOINT_SETTINGS
, WRITES_OTHER_SETTINGS
, WRITES_AUTOTUNE
, WRITES_FULL_THREAD_CKPT
From
V$INSTANCE_RECOVERY ;


Actual Target Log file size Log Chk. Timeout Log Chk. Interval
Recovery Estim. IOS Redo Blk. Redo Blk. Redo Blk. Redo Blk. Redo Blk.
------------------- ---------- ---------- ------------- ---------------- -----------------
1121 5278 8005 165888 8005


Fast Start IO Target Target Estimated Estim. Cluster
Redo Blk. MTTR MTTR Chk. Block Writes Optimal Logfile Size Available Time
-------------------- ---------- ---------- ----------------- -------------------- --------------
0 16 2100071


Writes MTTR Writes Logfile Size Writes Log Chk Set. Writes Other Set. Writes Autotune Writes Full Thread Ckpt
----------- ------------------- ------------------- ----------------- --------------- -----------------------
0 5174 0 0 2485565 933




rdbms-01:DB\sys> alter system set FAST_START_MTTR_TARGET = 300 scope=both ;

System altered.


apt-rdbms-01.aptus.co.uk:APTDB\sys> SELECT OPTIMAL_LOGFILE_SIZE OPTIMAL_LOGFILE_SIZE_MB FROM V$INSTANCE_RECOVERY;

OPTIMAL_LOGFILE_SIZE_MB
-----------------------
321






apt-rdbms-01.aptus.co.uk:APTDB\sys> SELECT TARGET_MTTR,ESTIMATED_MTTR,WRITES_MTTR,WRITES_LOGFILE_SIZE/1024/1024,OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;

Target Estimated
MTTR MTTR Writes MTTR WRITES_LOGFILE_SIZE/1024/1024 Optimal Logfile Size
---------- ---------- ----------- ----------------------------- --------------------
36 15 0 .004934311 321



checkpoint is not driven by WRITES_MTTR ie the FAST_START_MTTR parameter.because size is not optimal




apt-rdbms-01.aptus.co.uk:APTDB\sys> select BYTES/1024/1024 from v$log ;

BYTES/1024/1024
---------------
50 <<<<<<<<< --size should be 321 mb
50
50




Recommendation

All redo files should be same size .
small redo file can increase checkpoint activity and reduce performance
thumb rule is to switch logs every twenty minutes


ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 1200 SCOPE=BOTH; ---- for this set 20 min

or

through

cron

alter system archive log current;



Which means that Redo Logfile Size Advisor is enabled only if FAST_START_MTTR_TARGET is set.




some metalinke note

Note:265831.1 – Automatic Checkpoint Tuning in 10g
Note:274264.1 – REDO LOGS SIZING ADVISORY
Note 180894.1 – V$INSTANCE_RECOVERY
Note 151062.1 – Init.ora Parameter “FAST_START_MTTR_TARGET” Reference Note
Note 30754.1 – Init.ora Parameter “LOG_CHECKPOINT_INTERVAL” Reference Note
Note 30755.1 – Init.ora Parameter “LOG_CHECKPOINT_TIMEOUT” Reference Note
Note 68931.1 – Init.ora Parameter “FAST_START_IO_TARGET” Reference Note