Pages

Monday, September 11, 2023

MTTR Finding - use fast_start_mttr_target instead of log_checkpoint_timeout

When running AWR, the ADDM recommendation section suggest below findings:

Finding 3: Checkpoints Due to MTTR
Impact is .07 active sessions, 4.91% of total activity.
-------------------------------------------------------
Buffer cache writes due to setting of the obsolete parameters
"fast_start_io_target", "log_checkpoint_interval" and "log_checkpoint_timeout"
were consuming significant database time.
   Recommendation 1: Database Configuration
   Estimated benefit is .07 active sessions, 4.91% of total activity.
   ------------------------------------------------------------------
   Action
      Oracle's recommended solution is to control MTTR setting using the
      "fast_start_mttr_target" parameter instead of the
      "fast_start_io_target", "log_checkpoint_interval" and
      "log_checkpoint_timeout" parameters.

See related parameters in database:
show parameter fast_start_mttr_target
show parameter fast_start_io_target
show parameter log_checkpoint_interval
show parameter log_checkpoint_timeout
show parameter statistics_level
show parameter disk_asynch_io
show parameter db_writer_processes
  
fast_start_mttr_target               integer     0
fast_start_io_target                 integer     0
log_checkpoint_interval              integer     0
log_checkpoint_timeout               integer     1800
statistics_level                     string      TYPICAL
disk_asynch_io                       boolean     TRUE
db_writer_processes                  integer     2

SELECT target_mttr, estimated_mttr
  FROM  V$INSTANCE_RECOVERY;

TARGET_MTTR ESTIMATED_MTTR
----------- --------------
          0              0

Some Theory
The FAST_START_MTTR_TARGET
Initialization parameter is used to specify the number of seconds crash recovery should take. Oracle uses this target time to configure the the FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL parameters to reduce crash recovery time to a level as close to the target time as possible. 
The FAST_START_IO_TARGET, LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT parameters should not be set as they may interfere with the process.   
For Example:
ALTER SYSTEM SET fast_start_mttr_target=180 SCOPE=BOTH

LOG_CHECKPOINT_INTERVAL 
Specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. 
This number refers to physical operating system blocks, not database blocks.

LOG_CHECKPOINT_TIMEOUT 

Specifies (in seconds) the amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log (sometimes called the tail of the log) occurred. 
This parameter also signifies that no buffer will remain dirty (in the cache) for more than integer seconds.
Specifying a value of 0 for the timeout disables time-based checkpoints. 
Setting the value to 0 is not recommended unless FAST_START_MTTR_TARGET is set.

FAST_START_MTTR_TARGET 
Enables you to specify the number of seconds the database takes to perform crash recovery of a single instance.
When specified, FAST_START_MTTR_TARGET is overridden by LOG_CHECKPOINT_INTERVAL.
The default for fast_start_mttr_target is 300 seconds (5 Minutes). and maximum we can give 3600 ( 1 Hour).

Will it work in SE...? - No
Since the observation in the ADDM states that "Buffer cache writes due to setting of the obsolete parameters "fast_start_io_target", "log_checkpoint_interval" and "log_checkpoint_timeout"  were consuming significant database time. ", 
 need to set the MTTR for the database writer (DBWn) processes for the purpose of writing changed data blocks  from the Oracle buffer cache to disk and advancing the thread-checkpoint.

Sounds good, right...?
But oracle forget in recommendations, to tel you, that fast_start_mttr_target is available only in EE.

Attempt to use it in SE, will result in :

SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=180 SCOPE=BOTH;
ALTER SYSTEM SET FAST_START_MTTR_TARGET=180 SCOPE=BOTH
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00439: feature not enabled: Fast-Start Fault Recovery

Example:
ALTER SYSTEM SET FAST_START_MTTR_TARGET=180 SCOPE=BOTH;
ALTER SYSTEM SET LOG_CHECKPOINT_TIMEOUT=0 SCOPE=BOTH;
 
The RDBMS Standard Edition does not support FAST_START_MTTR_TARGET (one would need a Enterprise Edition for that).

SELECT parameter,value  
  FROM V$OPTION 
 WHERE parameter = 'Fast-Start Fault Recovery';

PARAMETER                      VALUE                          
------------------------------ ----------------
Fast-Start Fault Recovery      FALSE                                  

No comments:

Post a Comment