Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor!
doc的原話,隨手記錄一下!
[@more@]Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor
To determine the appropriate value for FAST_START_MTTR_TARGET
for your database, use the following four step process:
Calibrate the FAST_START_MTTR_TARGET
The FAST_START_MTTR_TARGET
initialization parameter causes the database to calculate internal system trigger values, in order to limit the length of the redo log and the number of dirty data buffers in the data cache. This calculation uses estimated time to read a redo block, estimates of the time to read and write a data block, as well as characteristics of typical workload of the system, such as how many dirty buffers corresponds to how many change vectors, and so on.
Initially, internal defaults are used in the calculation. These defaults are replaced over time by data gathered on I/O performance during system operation and actual cache recoveries.
You will have to perform several instance recoveries in order to calibrate your FAST_START_MTTR_TARGET
value properly. Before starting calibration, you must decide whether FAST_START_MTTR_TARGET
is being calibrated for a database crash or a hardware crash. This is a consideration if your database files are stored in a file system or if your I/O subsystem has a memory cache, because there is a considerable difference in the read and write time to disk depending on whether or not the files are cached. The appropriate value for FAST_START_MTTR_TARGET
will depend upon which type of crash is more important to recover from quickly.
To effectively calibrate FAST_START_MTTR_TARGET
, make sure that you run the typical workload of the system for long enough, and perform several instance recoveries to ensure that the time to read a redo block and the time to read or write a data block during recovery are recorded accurately.
Determine the Practical Range for FAST_START_MTTR_TARGET
After calibration, you can perform tests to determine the practical range for FAST_START_MTTR_TARGET
for your database.
Determining Lower Bound for FAST_START_MTTR_TARGET: Scenario
To determine the lower bound of the practical range, set FAST_START_MTTR_TARGET
to 1, and start up your database. Then check the value of V$INSTANCE_RECOVERY.TARGET_MTTR
, and use this value as a good lower bound for FAST_START_MTTR_TARGET
. Database startup time, rather than cache recovery time, is usually the dominant factor in determining this limit.
For example, set the FAST_START_MTTR_TARGET
to 1:
SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=1;
Then, execute the following query immediately after opening the database:
SQL> SELECT TARGET_MTTR, ESTIMATED_MTTR FROM V$INSTANCE_RECOVERY;
Oracle responds with the following:
TARGET_MTTR ESTIMATED_MTTR 18 15
The TARGET_MTTR
value of 18 seconds is the minimum MTTR target that the system can achieve, that is, the lowest practical value for FAST_START_MTTR_TARGET
. This minimum is calculated based on the average database startup time.
The ESTIMATED_MTTR
field contains the estimated mean time to recovery based on the current state of the running database. Because the database has just opened, the system contains few dirty buffers, so not much cache recovery would be required if the instance failed at this moment. That is why ESTIMATED_MTTR
can, for the moment, be lower than the minimum possible TARGET_MTTR
.
ESTIMATED_MTTR
can be affected in the short term by recent database activity. Assume that you query V$INSTANCE_RECOVERY
immediately after a period of heavy update activity in the database. Oracle responds with the following:
TARGET_MTTR ESTIMATED_MTTR 18 30
Now the effective MTTR target is still 18 seconds, and the estimated MTTR (if a crash happened at that moment) is 30 seconds. This is an acceptable result. This means that some checkpoints writes might not have finished yet, so the buffer cache contains more dirty buffers than targeted.
Now wait for sixty seconds and reissue the query to V$INSTANCE_RECOVERY
. Oracle responds with the following:
TARGET_MTTR ESTIMATED_MTTR 18 25
The estimated MTTR at this time has dropped to 25 seconds, because some of the dirty buffers have been written out during this period
Determining Upper Bound for FAST_START_MTTR_TARGET
To determine the upper bound of the practical range, set FAST_START_MTTR_TARGET
to 3600, and operate your database under a typical workload for a while. Then check the value of V$INSTANCE_RECOVERY.TARGET_MTTR
. This value is a good upper bound for FAST_START_MTTR_TARGET
.
The procedure is substantially similar to that in "Determining Lower Bound for FAST_START_MTTR_TARGET: Scenario".
Selecting Preliminary Value for FAST_START_MTTR_TARGET
Once you have determined the practical bounds for the FAST_START_MTTR_TARGET
parameter, select a preliminary value for the parameter. Choose a higher value within the practical range if your concern is with database performance, and a lower value within the practical range if your priority is shorter recovery times. The narrower the practical range, of course, the easier the choice becomes.
For example, if you discovered that the practical range was between 17 and 19 seconds, it would be quite simple to choose 19, because it makes relatively little difference in recovery time and at the same time minimizes the effect of checkpointing on system performance. However, if you found that the practical range was between 18 and 40 seconds, you might choose a compromise value of 30, and set the parameter accordingly:
SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=30;
You might then go on to use the MTTR Advisor to determine an optimal value.
Evaluate Different Target Values with MTTR Advisor
Once you have selected a preliminary value for FAST_START_MTTR_TARGET
, you can use MTTR Advisor to evaluate the effect of different FAST_START_MTTR_TARGET
settings on system performance, compared to your chosen setting.
Enabling MTTR Advisor
To enable MTTR Advisor, set the two initialization parameters STATISTICS_LEVEL
and FAST_START_MTTR_TARGET
.
STATISTICS_LEVEL
governs whether all advisors are enabled and is not specific to MTTR Advisor. Make sure that it is set to TYPICAL
or ALL
. Then, when FAST_START_MTTR_TARGET
is set to a non-zero value, the MTTR Advisor is enabled.
Using MTTR Advisor
After enabling MTTR Advisor, run a typical database workload for a while. When MTTR Advisor is ON, the database simulates checkpoint queue behavior under the current value of FAST_START_MTTR_TARGET
, and up to four other different MTTR settings within the range of valid FAST_START_MTTR_TARGET
values. (The database will in this case determine the valid range for FAST_START_MTTR_TARGET
itself before testing different values in the range.)
Viewing MTTR Advisor Results: V$MTTR_TARGET_ADVICE
The dynamic performance view V$MTTR_TARGET_ADVICE
lets you view statistics or advisories collected by MTTR Advisor.
The database populates V$MTTR_TARGET_ADVICE
with advice about the effects of each of the FAST_START_MTTR_TARGET
settings for your database. For each possible value of FAST_START_MTTR_TARGET
, the row contains details about how many cache writes would be performed under the workload tested for that value of FAST_START_MTTR_TARGET.
Specifically, each row contains information about cache writes, total physical writes (including direct writes), and total I/O (including reads) for that value of FAST_START_MTTR_TARGET
, expressed both as a total number of operations and a ratio compared to the operations under your chosen FAST_START_MTTR_TARGET
value. For instance, a ratio of 1.2 indicates 20% more cache writes.
Knowing the effect of different FAST_START_MTTR_TARGET
settings on cache write activity and other I/O enables you to decide better which FAST_START_MTTR_TARGET
value best fits your recovery and performance needs.
If MTTR Advisor is currently on, then V$MTTR_TARGET_ADVICE
shows the Advisor information collected. If MTTR Advisor is currently OFF
, the view shows information collected the last time MTTR Advisor was ON
since database startup, if any. If the database has been restarted since the last time the MTTR Advisor was used, or if it has never been used, the view will not show any rows.
Determine Optimal Size for Redo Logs
You can use the V$INSTANCE_RECOVERY
view column OPTIMAL_LOGFILE_SIZE
to determine the size of your online redo logs. This field shows the redo log file size in megabytes that is considered optimal based on the current setting of FAST_START_MTTR_TARGET
. If this field consistently shows a value greater than the size of your smallest online log, then you should configure all your online logs to be at least this size.
Note, however, that the redo log file size affects the MTTR. In some cases, you may be able to refine your choice of the optimal FAST_START_MTTR_TARGET
value by re-running the MTTR Advisor with your suggested optimal log file size.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/19602/viewspace-1013148/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- FAST_START_MTTR_TARGETAST
- 理解FAST_START_MTTR_TARGETAST
- FAST_START_MTTR_TARGET引數AST
- MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setAST
- 關於FAST_START_MTTR_TARGET引數AST
- 關於引數fast_start_mttr_targetAST
- 引數FAST_START_MTTR_TARGET的理解AST
- 10G FAST_START_MTTR_TARGET引數AST
- fast_start_mttr_target & log_checkpoint_timeoutAST
- [轉]FAST_START_MTTR_TARGET 幾個引數AST
- [20170515]引數fast_start_mttr_targetAST
- 11G中FAST_START_MTTR_TARGET引數AST
- SQL TUNING ADVISORSQL
- FAST_START_MTTR_TARGET引數是怎麼工作了AST
- SQL Access Advisor 與SQL Tuning AdvisorSQL
- sql tuning set/sql tuning advisor(待完善)SQL
- SQL Access Advisor、SQL Tuning Advisor 測試SQL
- SQL Tuning Advisor簡介SQL
- sql tuning advisor和sql access advisor區別SQL
- Sql Tuning Advisor 使用方法SQL
- 深入瞭解SQL Tuning AdvisorSQL
- SQL Tuning Advisor : dbms_sqltuneSQL
- SQL Tuning Advisor使用例項SQL
- 【OCP最新題庫解析(052)--題2】...is true about FAST_START_MTTR_TARGETAST
- fast_start_mttr_target快速啟動平均故障恢復的時間AST
- 對引數FAST_START_MTTR_TARGET = 0 的誤解及設定AST
- 手工執行sql tuning advisor和sql access advisorSQL
- ORACLE SQL TUNING ADVISOR 使用方法OracleSQL
- Guideline of SQL Tuning AdvisorGUIIDESQL
- 使用sql tuning advisor最佳化sqlSQL
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- Introduction to SQL Tuning Advisor zt自ITPUBSQL
- oracle實用sql(3)--sql tuning advisorOracleSQL
- Oracle優化案例-關閉auto space advisor和sql tuning advisor(十九)Oracle優化SQL
- sql tuning advisor(STA) 建議 建立sql profileSQL
- Oracle SQL優化之sql tuning advisor(STA)OracleSQL優化
- [原創]ORACLE SQL TUNING ADVISOR 使用方法OracleSQL
- Sql Tuning Advisor的大致過程測試!SQL