参数FAST_START_MTTR_TARGET的理解

一、FAST_START_MTTR_TARGET参数的作用和实现方法

 

参数FAST_START_MTTR_TARGET参数是一个加快实例恢复的参数,我们可以根据服务界别来定义一个合理的、可接受的值。该值得单位为秒。比如设定为60S,假定该值处于合理的情况之下,则一旦实例崩溃,在60S以内实例应当能够被恢复。合理即该值不能太大,也不能太小。太大则实例恢复所需的时间较长,太小则导致大量数据的及时写入,增加了系统的I/O

影响实例恢复时间长短的主要因素是从最近检查点位置到联机重做日志尾部之间的距离。距离越长则所需要的cache recoveryundoredo的时间越长。所以如何有效的缩短最近检查点位置与联机重做日志尾部之间的距离,正是FAST_START_MTTR_TARGET的目的。

FAST_START_MTTR_TARGET的值实际上是通过触发检查点来实现它的目的的。当内存中产生的dirty buffer所需的恢复时间(estimated_mttr)到达FAST_START_MTTR_TARGET所指定的时间,则检查点进程被触发。检查点进程一旦被触发,将通过DBWn进程按检查点队列顺序将脏数据写入到数据文件,从而缩短了最后检查点位置与联机重做日志间的距离,减少了实例恢复所需的时间。

 

二、FAST_START_MTTR_TARGET参数为0的情况

 

官网描述:

Fast-start checkpointing refers to the periodic writes by 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. Setting the database parameter FAST_START_MTTR_TARGET to a value greater than zero enables the fast-start checkpointing feature. Fast-start checkpointing should always be enabled for the following reasons:

 

It reduces the time required for cache recovery, and makes instance recovery time-bounded and predictable. This is accomplished  by limiting the number of dirty buffers (data blocks which have changes in memory that still need to be written to disk) and the number of redo records (changes in the database) generated between the most recent redo record and the last checkpoint.

 

Fast-Start checkpointing eliminates bulk writes and corresponding I/O spikes that occur traditionally with interval- based checkpoints, providing a smoother, more consistent I/O pattern that is more predictable and easier to manage. If the system is not   already near or at its maximum I/O capacity, fast-start checkpointing will have a negligible impact on performance. Although     fast-start checkpointing results in increased write activity, there is little reduction in database throughout, provided the system  has sufficient I/O capacity.

 

Explicit setting of the FAST_START_MTTR_TARGET parameter to 0 disables automatic checkpoint tuning.Explicit setting of the FAST_START_MTTR_TARGET parameter to a value other than 0 also enables the Redo Log Advisor.

 

从上面的描述可以看出,如果将FAST_START_MTTR_TARGET设置为0将关闭检查点自动调整功能。当设定一个大于0的值给FAST_START_MTTR_TARGET,则自动调整检查点功能将启用。

 

三、设置FAST_START_MTTR_TARGET

 

这个参数值得设定需要考虑到可接受的实例的恢复时间、可承受的I/O吞吐量等等。可以再系统正常负载时参考v$instance_recovery视图来设置该参数的值。

 

假定将FAST_TARGET_MTTR_TARGET的值设置为30S

SYS@ tsid > alter system set fast_start_mttr_target=30;

 

视图v$instance_recovery中有两个相关字段:TARGET_MTTRESTIMATED_MTTR

TARGET_MTTR:参照FAST_START_MTTR_TARGET参数中设定的值计算出来的一个值。

ESTIMATED_MTTR:系统根据dirty buffer中计算出来的值。

 

SYS@tsid>select recovery_estimated_ios,actual_redo_blks,target_redo_blks,target_mttr,estimated_mttr

  2  from v$instance_recovery;

 

RECOVERY_ESTIMATED_IOS ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR

---------------------- ---------------- ---------------- ----------- --------------

                  1768           104036           184320          28             23

 

 

四、启用MTTR Advisor

 

首先需要设置两个参数:STATISTICS_LEVEL,设置为typical或者all

                       FAST_START_MTTR_TARGET,设置为非0值。

 

SYS@ tsid > show parameter mttr;  --当前参数设置为30S

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------

fast_start_mttr_target               integer     30

 

   

SYS@ tsid > select target_mttr,estimated_mttr from v$instance_recovery;

 

TARGET_MTTR ESTIMATED_MTTR

----------- --------------

         28             23                     --系统计算出的mttr28S

 

SYS@tsid>select mttr_target_for_estimate,dirty_limit,estd_cache_writes,estd_cache_write_factor,estd_total_writes,estd_total_write_factor from v$mttr_target_advice;

 

MTTR_TARGET_FOR_ESTIMATE DIRTY_LIMIT ESTD_CACHE_WRITES ESTD_CACHE_WRITE_FACTOR ESTD_TOTAL_WRITES ESTD_TOTAL_WRITE_FACTOR

------------------------ ----------- ----------------- ----------------------- ----------------- -----------------------

                      27        1000             75103                  1.0043             75296              1.0043

                      28        1103             74974                  1.0026             75167              1.0026

                      30        1313             74781                       1             74974                   1

                      31        1417             74652                   .9983             74845               .9983

                      32        1522             74587                   .9974             74780               .9974

 

--mttr_target_for_estimate有一个值最接近设定的目标时间30

--给出了几组不同的mttr_target值及dirty_limitcache_writeio来供DBA来选择设定合适的mttr

上一篇:excel文件内容导入数据库的问题及解决


下一篇:pythonchallenge学到的python内置函数整理