Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 30 June 2012

Instance Recovery Performance Tuning

Fast-Start Time-Based Recovery (FSTBR) was introduced in Oracle9i for the purpose of being able to define a target mean time to recover (MTTR) in the case of crash or instance recovery. With FSTBR, a desired MTTR for the database is defined in seconds. This is done using the FAST_START_MTTR_TARGET parameter. Once this parameter is set, Oracle will configure internal database settings to try and maintain this requirement.
Note: The FAST_START_MTTR_TARGET parameter provides only an estimated MTTR value. Several factors such as IO contention can cause the actual recovery time to extend beyond the requested MTTR.
Instance Recovery Performance Tuning
Instance and crash recovery are automatic procedure. During a normal operation if database instance failure (either ingle or multiple in RAC Env.) then oracle automatically perform crash recovery at the next startup.
The database will open again as soon as the cache recovery is completed. During these recovery:
Rolling forward: Oracle applies all committed and uncommitted changes in the redo log files to the affected data blocks.
Rolling Backward: Oracle applies the rollback segments to undo the uncommitted changes to make the database consistent.
Checkpointing: Frequent checkpointing (writes dirty buffers to the datafiles) will reduce cache recovery time. The duration of cache recovery processing is determined by two factors: the number of data blocks that have changes at SCNs higher than the SCN of the checkpoint, and the number of log blocks that need to be read to find those changes.
Note: In a high-update system, frequent checkpointing increases the overhead for normal database operations.
How to configure the duration of Cache Recovery:
FAST_START_MTTR_TARGET specifies a target for the expected mean time to recover (MTTR), the time (in seconds) that it should take to start up the instance and perform cache recovery. Once FAST_START_MTTR_TARGET is set, the database manages incremental checkpoint writes in an attempt to meet that target. If you have chosen a practical value for FAST_START_MTTR_TARGET, you can expect your database to recover, on average, in approximately the number of seconds you have chosen.
Maximum value for FAST_START_MTTR_TARGET = 3600 seconds.
If you try to set the value more than 3600, then Oracle rounds it to 3600.
How to Monitoring Cache Recovery
WHERE NAME='fast_start_mttr_target';
To check the current value of fast_start_mttr_target in database
It displays the current recovery parameter setting. You can also use statistics from this view to determine which factor has the greatest influence on checkpointing.
It will display effective mean time to recover (MTTR) target in seconds and the current estimated mean time to recover (MTTR) in seconds based on the current dirty buffers and log blocks.
For a regular Monitoring of your database, you can periodically compare V$INSTANCE_RECOVERY.TARGET_MTTR to your FAST_START_MTTR_TARGET. The two values should generally be the same if the FAST_START_MTTR_TARGET value is in the practical range. If TARGET_MTTR is consistently longer than FAST_START_MTTR_TARGET, then set FAST_START_MTTR_TARGET to a value no less than TARGET_MTTR. If TARGET_MTTR is consistently shorter, then set FAST_START_MTTR_TARGET to a value no greater than TARGET_MTTR.
The below four steps is used to determine the appropriate value for FAST_START_MTTR_TARGET for your database
§         Calibrate the FAST_START_MTTR_TARGET
§         Determine the Practical Range for FAST_START_MTTR_TARGET
§         Evaluate Different Target Values with MTTR Advisor
§         Determine Optimal Size for Redo Logs
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.
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 Range for FAST_START_MTTR_TARGET
Here by setting the higher bound and lower bound FAST_START_MTTR_TARGET and performing test on these value we can determine the practical range for FAST_START_MTTR_TARGET
Determining Lower and Upper bound:
16          14           
The TARGET_MTTR value of 14 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:
16          25           
Now the effective MTTR target is still 16 seconds, and the estimated MTTR (if a crash happened at that moment) is 25 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 1 minute reissue the query, and check the oracle responds.
16          21  
The estimated MTTR at this time has dropped to 21 seconds, because some of the dirty buffers have been written out during this period.
To determine the upper bound of the practical range the procedure is almost same as above, 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.
Once you have determine the upper bound and lower bound of range by operating the database under the typical load, select the preliminary value for FAST_START_MTTR_TARGET > Avg of the Determined  lower/upper bound list range.
For Example: If you find the lower/upper bound list range = 16-32 seconds then preliminary value > (16+32)/2 that is choose the value 25.
Evaluate Different Target Values with MTTR Advisor
To enable MTTR advisor set the initialization parameter STATISTICS_LEVEL (either TYPICAL or ALL) and the value of FAST_START_MTTR_TARGET is non zero.
Now run the database under typical workload and read the value from V$MTTR_TARGET_ADVICE with different FAST_START_MTTR_TARGET setting.
Estimate DIRTY_LIMIT Cache_Writes     Factor Total_Writes  Total_IOS
---------- ----------- ------------ ---------- ------------ ----------
        36        1000         3385          1         3385      90325
        54        3000         3385          1         3385      90325
Note: If Advisor is currently ON, then the view 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.


  1. Quantum Binary Signals

    Professional trading signals delivered to your cell phone every day.

    Follow our signals NOW and gain up to 270% a day.