参数FAST_START_MTTR_TARGET的理解
本文来源:keeptrying 《参数FAST_START_MTTR_TARGET的理解》
参数FAST_START_MTTR_TARGET的理解
一、FAST_START_MTTR_TARGET参数的作用和实现方法
参数FAST_START_MTTR_TARGET参数是一个加快实例恢复的参数,我们可以根据服务界别来定义一个合理的、可接受的值。该值得单位为秒。比如设定为60S,假定该值处于合理的情况之下,则一旦实例崩溃,在60S以内实例应当能够被恢复。合理即该值不能太大,也不能太小。太大则实例恢复所需的时间较长,太小则导致大量数据的及时写入,增加了系统的I/O。
影响实例恢复时间长短的主要因素是从最近检查点位置到联机重做日志尾部之间的距离。距离越长则所需要的cache recovery和undo、redo的时间越长。所以如何有效的缩短最近检查点位置与联机重做日志尾部之间的距离,正是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:
视图v$instance_recovery中有两个相关字段:TARGET_MTTR和ESTIMATED_MTTR ;TARGET_MTTR:参照FAST_START_MTTR_TARGET参数中设定的值计算出来的一个值。ESTIMATED_MTTR:系统根据dirty buffer中计算出来的值。
1 SYS@tsid>select recovery_estimated_ios,actual_redo_blks,target_redo_blks,target_mttr,estimated_mttr from v$instance_recovery; 3 4 RECOVERY_ESTIMATED_IOS ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR 5 ---------------------- ---------------- ---------------- ----------- -------------- 6 1768 104036 184320 28 23
四、启用MTTR Advisor
首先需要设置两个参数:STATISTICS_LEVEL,设置为typical或者all ;FAST_START_MTTR_TARGET,设置为非0值。
1 SYS@ tsid > show parameter mttr; --当前参数设置为30S 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------ 5 fast_start_mttr_target integer 30 6 7 8 SYS@ tsid > select target_mttr,estimated_mttr from v$instance_recovery; 9 10 TARGET_MTTR ESTIMATED_MTTR 11 ----------- -------------- 12 28 23 --系统计算出的mttr为28S 13 14 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; 15 16 MTTR_TARGET_FOR_ESTIMATE DIRTY_LIMIT ESTD_CACHE_WRITES ESTD_CACHE_WRITE_FACTOR ESTD_TOTAL_WRITES ESTD_TOTAL_WRITE_FACTOR 17 ------------------------ ----------- ----------------- ----------------------- ----------------- ----------------------- 18 27 1000 75103 1.0043 75296 1.0043 19 28 1103 74974 1.0026 75167 1.0026 20 30 1313 74781 1 74974 1 21 31 1417 74652 .9983 74845 .9983 22 32 1522 74587 .9974 74780 .9974 23 24 --mttr_target_for_estimate有一个值最接近设定的目标时间30。 25 --给出了几组不同的mttr_target值及dirty_limit,cache_write,io来供DBA来选择设定合适的mttr。
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
学问:纸上得来终觉浅,绝知此事要躬行
为事:工欲善其事,必先利其器。
态度:道阻且长,行则将至;行而不辍,未来可期
.....................................................................
------- 桃之夭夭,灼灼其华。之子于归,宜其室家。 ---------------
------- 桃之夭夭,有蕡其实。之子于归,宜其家室。 ---------------
------- 桃之夭夭,其叶蓁蓁。之子于归,宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客,仅做学习讨论之用,不做商业用途。
* 如有侵权,马上联系我,我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处! ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/