引數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：

SYS@ tsid > alter system set fast_start_mttr_target=30;

檢視v$instance_recovery中有兩個相關欄位：TARGET_MTTR和ESTIMATED_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                     --系統計算出的mttr為28S

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_limit，cache_write，io來供DBA來選擇設定合適的mttr。