Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) Part Number A96533-02 |
|
|
View PDF |
This chapter offers guidelines for configuring the time to perform instance recovery.
This chapter contains the following sections:
Instance and crash recovery are the automatic application of redo log records to Oracle data blocks after a crash or system failure. During normal operation, if an instance is shutdown cleanly as when using a SHUTDOWN
IMMEDIATE
statement, rather than terminated abnormally, then the in-memory changes that have not already been written to the datafiles on disk are written to disk as part of the checkpoint performed during shutdown.
However, if a single instance database crashes or if all instances of an Oracle Real Application Cluster configuration crash, then Oracle performs crash recovery at the next startup. If one or more instances of an Oracle Real Application Cluster configuration crash, then a surviving instance performs instance recovery automatically. Instance and crash recovery occur in two steps: cache recovery followed by transaction recovery.
During the cache recovery step, Oracle applies all committed and uncommitted changes in the redo log files to the affected data blocks. The work required for cache recovery processing is proportional to the rate of change to the database (update transactions each second) and the time between checkpoints.
To make the database consistent, the changes that were not committed at the time of the crash must be undone (in other words, rolled back). During the transaction recovery step, Oracle applies the rollback segments to undo the uncommitted changes. The work required to do transaction recovery is proportional to the number and size of uncommitted transactions when the system fault occurred.
Periodically, Oracle records a checkpoint. A checkpoint is the highest system change number (SCN) such that all data blocks less than or equal to that SCN are known to be written out to the data files. If a failure occurs, then only the redo records containing changes at SCNs higher than the checkpoint need to be applied during recovery. 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.
Frequent checkpointing writes dirty buffers to the datafiles more often than otherwise, and so reduces cache recovery time in the event of an instance failure. If checkpointing is frequent, then applying the redo records in the redo log between the current checkpoint position and the end of the log involves processing relatively few data blocks. This means that the cache recovery phase of recovery is fairly short.
However, in a high-update system, frequent checkpointing can reduce runtime performance, because checkpointing causes DBWn processes to perform writes.
To minimize the duration of instance recovery, you must force Oracle to checkpoint often, thus keeping the number of redo log records to be applied during recovery to a minimum. However, in a high-update system, frequent checkpointing increases the overhead for normal database operations.
If daily operational efficiency is more important than minimizing recovery time, then decrease the frequency of writes to data files due to checkpoints. This should improve operational efficiency, but also increase instance recovery time.
See Also:
|
To reduce the checkpoint frequency and optimize runtime performance, you can do the following:
LOG_CHECKPOINT_INTERVAL
initialization parameter (in multiples of physical block size) to zero. This value eliminates interval checkpoints.LOG_CHECKPOINT_TIMEOUT
initialization parameter to zero. This value eliminates time-based checkpoints.FAST_START_MTTR_TARGET
(and FAST_START_IO_TARGET)
to zero to disable fast-start checkpointing.
See Also:
Oracle9i Database Concepts for a complete discussion of checkpoints |
There are several methods for tuning cache recovery to keep the duration of recovery within user-specified bounds. These include the following:
The initialization parameters in Example 17-1 influence cache recovery time.
Oracle Enterprise Edition features include a fast-start fault recovery functionality to control instance recovery. This reduces the time required for cache recovery and makes the recovery bounded and predictable by limiting the number of dirty buffers and the number of redo records generated between the most recent redo record and the last checkpoint.
The foundation of fast-start recovery is the fast-start checkpointing architecture. Instead of the conventional event driven (that is, log switching) checkpointing, which does bulk writes, fast-start checkpointing occurs incrementally. Each DBWn process periodically writes buffers to disk to advance the checkpoint position. The oldest modified blocks are written first to ensure that every write lets the checkpoint advance. Fast-start checkpointing eliminates bulk writes and the resultant I/O spikes that occur with conventional checkpointing.
Administrators specify a target (bounded) time to complete the cache recovery phase of recovery with the FAST_START_MTTR_TARGET
initialization parameter, and Oracle automatically varies the incremental checkpoint writes to meet that target. The FAST_START_MTTR_TARGET
initialization parameter lets you specify in seconds the expected mean time to recover (MTTR), which is the expected amount of time Oracle takes to perform crash or instance recovery for a single instance.
The FAST_START_MTTR_TARGET
initialization parameter simplifies the configuration of recovery time from instance or system failure. This parameter lets you specify the number of seconds crash or instance recovery is expected to take. The FAST_START_MTTR_TARGET
is internally converted to a set of parameters that modify the operation of Oracle such that recovery time is as close to this estimate as possible.
The maximum value for FAST_START_MTTR_TARGET
is 3600, or one hour. If you set the value to more than 3600, then Oracle rounds it to 3600. There is no minimum value for FAST_START_MTTR_TARGET
. However, this does not mean that you can target the recovery time as low as you want. The time to do a crash recovery is limited by the low limit of the target number of dirty buffers, which is 1000, as well as factors such as how long initialization and file open take.
If you set the value of FAST_START_MTTR_TARGET
too low, then the effective mean time to recover (MTTR) target will be the best MTTR target the system can achieve. If you set the value of FAST_START_MTTR_TARGET
to such a high value that even in the worst-case recovery would not take that long, then the effective MTTR target will be the estimated MTTR in the worst-case scenario when the whole buffer cache is dirty. Use the TARGET_MTTR
column in the V$INSTANCE_RECOVERY
view to see the effective MTTR.
See Also:
"Monitoring Estimated MTTR: Example Scenario" for more information on setting |
Set the initialization parameter LOG_CHECKPOINT_TIMEOUT
to an integer value n to require that the latest checkpoint position follow the most recent redo block by no more than n seconds. In other words, at most, n seconds worth of logging activity can occur between the most recent checkpoint position and the last block written to the redo log. This forces the checkpoint position to keep pace with the most recent redo block.
You can also interpret LOG_CHECKPOINT_TIMEOUT
as specifying an upper bound on the time a buffer can be dirty in the cache before DBWn must write it to disk. For example, if you set LOG_CHECKPOINT_TIMEOUT
to 60, then no buffers remain dirty in the cache for more than 60 seconds. The default value for LOG_CHECKPOINT_TIMEOUT
is 1800, or 30 minutes.
Set the initialization parameter LOG_CHECKPOINT_INTERVAL
to a value n (where n is an integer) to require that the checkpoint position never follow the most recent redo block by more than n blocks. In other words, at most n redo blocks can exist between the checkpoint position and the last block written to the redo log. In effect, you are limiting the amount of redo blocks that can exist between the checkpoint and the end of the log.
Oracle limits the maximum value of LOG_CHECKPOINT_INTERVAL
to 90% of the smallest log to ensure that the checkpoint advances into the current log before that log fills and a log switch is attempted.
LOG_CHECKPOINT_INTERVAL
is specified in redo blocks. Redo blocks are the same size as operating system blocks. Use the LOG_FILE_SIZE_REDO_BLKS
column in V$INSTANCE_RECOVERY
to see the number of redo blocks corresponding to 90% of the size of the smallest log file.
See Also:
|
Use parallel recovery to tune the cache recovery phase of recovery. Parallel recovery uses a division of labor approach to allocate different processes to different data blocks during the cache recovery phase of recovery.
For example, during recovery the redo log is read, and blocks that require redo application are parsed out. These blocks are subsequently distributed evenly to all recovery processes to be read into the buffer cache. Crash, instance, and media recovery of datafiles on different disk drives are good candidates for parallel recovery.
Use the RECOVERY_PARALLELISM
initialization parameter to specify the number of concurrent recovery processes for instance or crash recovery. To use parallel processing, the value of RECOVERY_PARALLELISM
must be greater than 1 and cannot exceed the value of the PARALLEL_MAX_SERVERS
initialization parameter.
The LOG_PARALLELISM
initialization parameter allows the parallel generation of redo and can increase the throughput of certain update-intensive workloads. If you are using Oracle on high-end servers that have more than 16 processors, and you are experiencing very high contention on the redo allocation latch, then you should consider enabling parallel redo.
Recovery is usually I/O bound on reads to data blocks. Consequently, parallelism at the block level can only help recovery performance if it speeds up total I/Os. Performance on systems with efficient asynchronous I/O typically does not improve significantly with parallel recovery, unless the recovery is CPU-bound.
See Also:
Oracle9i Database Reference for more information on initialization parameters |
Use the V$INSTANCE_RECOVERY
view to see the current recovery parameter settings. You can also use statistics from this view to calculate which parameter has the greatest influence on checkpointing. V$INSTANCE_RECOVERY
contains the columns shown in Table 17-2.
See Also:
Oracle9i Database Reference for more information on the |
The TARGET_MTTR
field of V$INSTANCE_RECOVERY
contains the MTTR target in effect. The ESTIMATED_MTTR
field of V$INSTANCE_RECOVERY
contains the estimated MTTR should a crash happen right away. Query these two fields to see if the system can keep up with your specified MTTR target.
For example, assume the initialization parameter setting is as follows:
FAST_START_MTTR_TARGET = 6 # seconds
Execute the following query after database open:
SELECT TARGET_MTTR, ESTIMATED_MTTR, CKPT_BLOCK_WRITES FROM V$INSTANCE_RECOVERY;
Oracle responds with the following:
TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES 18 15 0
You see that TARGET_MTTR
is 18 seconds, which is higher than the value of FAST_START_MTTR_TARGET
specified (6 seconds). This means that it is impossible to recover the database within 6 seconds. 18 seconds is the minimum MTTR target that the system can achieve.
The 18 second minimum is calculated based on the absolute low limit of 1000 blocks on the target of number of dirty buffers (The deprecated initialization parameter FAST_START_IO_TARGET
follows this low limit; that is, you cannot set FAST_START_IO_TARGET
to less than 1000). The ESTIMATED_MTTR
field contains the estimated mean time to recovery. Because the database has just opened, the system contains few dirty buffers. That is why ESTIMATED_MTTR
can be lower than the minimum possible TARGET_MTTR
.
Now assume that you use the following statement to modify FAST_START_MTTR_TARGET
:
ALTER SYSTEM SET FAST_START_MTTR_TARGET = 30;
Reissue the query to V$INSTANCE_RECOVERY
, and Oracle responds with the following:
TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES 30 15 0
The ESTIMATED_MTTR
field is still 15 seconds, which means that the estimated MTTR at the current time (should a crash happen immediately) is still 15 seconds. This is because no new redo is written, and no data block has become dirty.
Assume that heavy update activity occurs in the database and then you query V$INSTANCE_RECOVERY
immediately afterward. Oracle responds with the following:
TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES 30 36 54367
You see that the effective MTTR target is 30 seconds. The estimated MTTR at the current time (should a crash happen immediately) is 36 seconds. This is fine. This means that some checkpoints writes might not have finished yet, so the buffer cache contains more dirty buffers than targeted.
Assume that you wait for one minute and reissue the query to V$INSTANCE_RECOVERY
. Oracle responds with the following:
TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES 30 31 55230
The estimated MTTR at this time has dropped to 31 seconds. This is because more dirty buffers have been written out during this period. This is shown by the increase of CKPT_BLOCK_WRITES
field of V$INSTANCE_RECOVERY
.
Note: The number of |
To calculate performance overhead, use the V$SYSSTAT
view. For example, assume that you execute the following query:
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('physical reads','physical writes', 'physical writes non checkpoint');
Oracle responds with the following:
NAME VALUE physical reads 2376 physical writes 14932 physical writes non checkpoint 11165
The first row shows the number of data blocks retrieved from disk. The second row shows the number of data blocks written to disk. The last row shows the number of writes to disk that would occur if you turned off checkpointing.
Use this data to calculate the overhead imposed by setting the FAST_START_MTTR_TARGET
initialization parameter. To effectively measure the percentage of extra writes, mark the values for these statistics at different times, t_1
and t_2
.
Calculate the percentage of extra I/Os generated by fast-start checkpointing using the following formula:
[((PW_2 - PW_1) - (PWNC_2 - PWNC_1)) / ((PR_2 - PR_1) + (PW_2 - PW_1))] x 100% = EIO
where the variables are described in Table 17-3.
It can take some time for database statistics to stabilize after instance startup or dynamic initialization parameter modification. After such events, wait until all blocks age out of the buffer cache at least once before taking measurements. If the percentage of extra I/Os is too high, then increase the value of FAST_START_MTTR_TARGET
.
The number of extra writes caused by setting FAST_START_MTTR_TARGET
to a nonzero value is application-dependent; it is not dependent on cache size.
As an example, assume the initialization parameter setting is as follows:
FAST_START_MTTR_TARGET = 90 # 90 seconds
After the statistics stabilize, you issue this query on V$SYSSTAT
:
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('physical reads','physical writes', 'physical writes non checkpoint');
Oracle responds with the following:
NAME VALUE physical reads 2376 physical writes 14932 physical writes non checkpoint 11165
The physical
write
checkpoint
statistics can also be found in the CKPT_BLOCK_WRITES
field of the V$INSTANCE_RECOVERY
view. For example:
SELECT CKPT_BLOCK_WRITES FROM V$INSTANCE_RECOVERY;
Oracle responds with the following:
CKPT_BLOCK_WRITES 3767
It is consistent with the result from V$SYSSTAT
: 3767 = 14932 - 11165.
After making updates for a few hours, you reissue the query. Oracle responds with the following:
NAME VALUE physical reads 3011 physical writes 17467 physical writes non checkpoint 13231
Substitute the values from the SELECT
statements in the formula to determine how much performance overhead you are incurring:
[((17467 - 14932) - (13231 - 11165)) / ((3011 - 2376) + (17467 - 14932))] x 100% = 14.8%
As the result indicates, enabling fast-start checkpointing generates about 15% more I/O than required had you not enabled fast-start checkpointing. After calculating the extra I/O, you decide you can afford more system overhead if you decrease recovery time.
To decrease recovery time, reduce the value for the parameter FAST_START_MTTR_TARGET
to 60. After items in the buffer cache age out, calculate V$SYSSTAT
statistics across a second interval to determine the new performance overhead. Query V$SYSSTAT
:
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('physical reads', 'physical writes', 'physical writes non checkpoint');
Oracle responds with the following:
NAME VALUE physical reads 4652 physical writes 28864 physical writes non checkpoint 21784
After making updates, reissue the query. Oracle responds with the following:
NAME VALUE physical reads 6000 physical writes 35394 physical writes non checkpoint 26438
Calculate how much performance overhead you are incurring using the values from the two SELECT
statements:
[(35394 - 28864) - (26438 - 21784)) / ((6000 - 4652) + (35394 - 28864))] x 100% = 23.8%
After changing the parameter, the percentage of I/Os performed by Oracle is now about 24% more than it would be if you disabled fast-start checkpointing.
The FAST_START_MTTR_TARGET
initialization parameter calculates internal system trigger values to limit the length of the redo log and the number of dirty data buffers in the data cache. This calculation uses estimated times to read a redo block and to read and write a data block.
Initially, internal defaults are used. These defaults are replaced by execution time estimates during system operation. However, the best values are obtained from measurements taken from an actual recovery from a failure.
Before doing instance recoveries to calibrate the FAST_START_MTTR_TARGET
, 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 workload being run during the instance recovery should be a very good representation of the average workload on the system to ensure that the amount of redo records generated are similar.
Starting with Oracle9i Release 2 (9.2), MTTR advisory is available to help you evaluate the effect of different MTTR settings on system performance in terms of extra physical writes.
When MTTR advisory is enabled, after the system runs a typical workload for a while, you can query V$MTTR_TARGET_ADVICE
, which tells you the ratio of estimated number of cache writes under other MTTR settings to the number of cache writes under the current MTTR. For instance, a ratio of 1.2 indicates 20% more cache writes.
By looking at the different MTTR settings and their corresponding cache write ratio, you can decide which MTTR value fits your recovery and performance needs. V$MTTR_TARGET_ADVICE
also gives the ratio on total physical writes (including direct writes), and the ratio on total I/Os (including reads).
Enabling MTTR Advisory involves setting two initialization parameters:
Make sure that STATISTICS_LEVEL
is set to TYPICAL
or ALL
.
To enable MTTR advisory, set the initialization parameter FAST_START_MTTR_TARGET
to a nonzero value. If FAST_START_MTTR_TARGET
is not specified, then MTTR advisory will be OFF
.
When MTTR advisory is ON
, it simulates checkpoint queue behavior under five different MTTR settings:
FAST_START_MTTR_TARGET
settingOracle9i Release 2 (9.2) provides a dynamic performance view for viewing statistics or advisories collected by MTTR advisory.
If MTTR advisory has been turned on, V$MTTR_TARGET_ADVICE shows the advisory information collected. Usually this view show five rows, corresponding to the current MTTR, 0.1 times the current MTTR, 0.5 times the current MTTR, 1.5 times the current MTTR and 2 times the current MTTR. However, if one or more of the 5 values are less than the smallest MTTR target the system can sustain, their corresponding rows are replaced with a single row corresponding to the smallest MTTR target the system can have. Similarly, if one or more of the 5 values are larger than the worst-case MTTR target the system can have, their corresponding rows are replaced with a single row corresponding to the worst-case MTTR target the system can have.
If MTTR advisory is currently OFF
, the view shows information collected the last time MTTR advisory was on.
See Also:
"V$MTTR_TARGET_ADVICE" for column details of these views |
During the second phase of instance recovery, Oracle rolls back uncommitted transactions. Oracle uses two features, fast-start on-demand rollback and fast-start parallel rollback, to increase the efficiency of this recovery phase.
Note: These features are part of fast-start fault recovery and are only available in the Oracle9i Enterprise Edition. |
This section contains the following topics:
Using the fast-start on-demand rollback feature, Oracle automatically allows new transactions to begin as soon as the database opens, which is usually a very short time after cache recovery completes. If a user attempts to access a row that is locked by a terminated transaction, Oracle rolls back only those changes necessary to complete the transaction; in other words, it rolls them back on demand. Consequently, new transactions do not have to wait until all parts of a long transaction are rolled back.
Note: Oracle does this automatically. You do not need to set any parameters or issue statements to use this feature. |
In fast-start parallel rollback, the background process SMON acts as a coordinator and rolls back a set of transactions in parallel using multiple server processes. Essentially, fast-start parallel rollback is to transaction recovery what parallel recovery is to cache recovery.
Fast-start parallel rollback is mainly useful when a system has transactions that run a long time before committing, especially parallel INSERT
, UPDATE
, and DELETE
operations. SMON automatically decides when to begin parallel rollback and disperses the work among several parallel processes: process one rolls back one transaction, process two rolls back a second transaction, and so on.
One special form of fast-start parallel rollback is intra-transaction recovery. In intra-transaction recovery, a single transaction is divided among several processes. For example, assume eight transactions require recovery with one parallel process assigned to each transaction. The transactions are all similar in size except for transaction five, which is quite large. This means it takes longer for one process to roll this transaction back than for the other processes to roll back their transactions.
In this situation, Oracle automatically begins intra-transaction recovery by dispersing transaction five among the processes: process one takes one part, process two takes another part, and so on.
You control the number of processes involved in transaction recovery by setting the initialization parameter FAST_START_PARALLEL_ROLLBACK
to one of three values listed in Table 17-4.
In Oracle Real Application Clusters, you can perform fast-start parallel rollback on each instance. Within each instance, you can perform parallel rollback on transactions that are:
After a rollback segment is online for a given instance, only this instance can perform parallel rollback on transactions on that segment.
Monitor the progress of fast-start parallel rollback by examining the V$FAST_START_SERVERS
and V$FAST_START_TRANSACTIONS
views. V$FAST_START_SERVERS
provides information about all recovery processes performing fast-start parallel rollback. V$FAST_START_TRANSACTIONS
contains data about the progress of the transactions.
See Also:
|