When running AWR, the ADDM recommendation section suggest below findings:
Finding 3: Checkpoints Due to MTTR
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
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.
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.
-------------------------------------------------------
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
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:
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_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
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.
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
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
------------------------------ ----------------
Fast-Start Fault Recovery FALSE
No comments:
Post a Comment