This parameter was introduced in Oracle 8.0.4. DB_WRITER_PROCESSES cannot be greater than CPU COUNT.
DB_WRITER_PROCESSES controls the number of 'DBW' gatherer processes being used by an instance. As of 8.0.4 the 'DBWR' process shows up as 'DBW0' and additional DB writers can be started using this parameter. DBWR operates in a batch-oriented mode: it gathers dirty buffers into a batch and then issues the writes for all buffers in the batch. The writes will use asynchronous IO (AIO) if possible. If AIO is not available then Oracle7 / Oracle8 allow the IO portion of this operation to be parallelisedby using multiple
DB_WRITERS / DBWR_IO_SLAVES respectively .
Setting DB_WRITER_PROCESSES parallelises both the gathering and writing of buffers thus parallelising more of the DBWR activity. Multiple DB writers should be able to produce higher throughput than one
DBWR with multiple slaves.
Checkpointing is of 2 types:
1. Normal checkpoint.
2. Incremental checkpoint.
1. Normal checkpoint will update the control file as well as all datafile headers.
2. Incremental checkpoint will update only the control file.
Incremental checkpoint is:
> Continuously active checkpoint
> no completion RBA
> checkpoint advanced in memory only
> RBA for incremental checkpoint recorded in control file.
> DBW0 writes out dirty buffers to advance the incremental checkpoint.
> Used to reduce recovery time after a failure.
Incremental checkpoint is determined by:
> Upper bound on recovery needs.
> size of the smallest log file
> value of log_checkpoint_interval
> value of log_checkpoint_timeout
> Total numbers of dirty buffers in the cache.
Starting with Oracle Database 10g, the database can self-tune checkpointing
to achieve good recovery times with low impact on normal throughput. With
automatic checkpoint tuning, Oracle Database takes advantage of periods of
low I/O usage to write out data modified in memory to the data files without
adverse impact on the throughput. Thus, a reasonable crash recovery time can
be achieved even if the administrator does not set any checkpoint-related
parameter or if this parameter is set to a very large value.
Oracle Database 10g supports automatic checkpoint tuning which takes
advantage of periods of low I/O usage to advance
checkpoints and therefore improve availability. Automatic checkpoint tuning is in
effect if the FAST_START_MTTR_TARGET
database initialization parameter is set to a nonzero value. Observe the following
recommendations to take advantage of automatic checkpoint tuning.
If it is necessary to control the time to recover from an instance or node failure, then set FAST_START_MTTR_TARGET to the
desired MTTR in seconds. If targeting a specific MTTR is unnecessary, then set FAST_START_MTTR_TARGET to a nonzero value to enable automatic
checkpoint tuning. Fast-start checkpointing can be disabled
by setting FAST_START_MTTR_TARGET=0.
Disable fast-start checkpointing only when system I/O capacity is insufficient
checkpointing enabled and achieving a target MTTR is not important.
Enabling fast-start checkpointing increases the average number of writes per
transaction that DBWn issues for a given workload (when compared with
disabling fast-start checkpointing). However, if the system
is not already near or at its maximum I/O capacity, then fast-start checkpointing
has a negligible impact on performance. The percentage of additional DBWn writes
with very aggressive fast-start checkpointing depends on many factors, including the
workload, I/O speed and capacity, CPU speed and capacity,
and the performance of previous recoveries.
If FAST_START_MTTR_TARGET is set to a low value, then fast-start checkpointing
is more aggressive, and the average number of writes per transaction that DBWn
issues is higher in order to keep the thread checkpoint sufficiently advanced to meet the
Conversely, if FAST_START_MTTR_TARGET is set to a high value,
or if automatic checkpoint tuning is in effect
(that is, FAST_START_MTTR_TARGET is set to a nonzero value),
then fast-start checkpointing in less aggressive, and the average number of writes
per transaction that DBWn issues is lower.
Fast-start checkpointing can be explicitly disabled by setting
FAST_START_MTTR_TARGET=0. Disabling fast-start checkpointing
leads to the fewest average number of writes per transaction for DBWn for a
specific workload and configuration, but also
results in the highest MTTR.
DB_BLOCK_CHECKING is used to control whether block checking is done
for transaction managed blocks. As early detection of corruptions
is useful, and has only a small performance impact,
Oracle recommends that the default setting be used FOR NEW DATABASES but note the following warning. ** WARNING ** The overhead of DB_BLOCK_CHECKING = TRUE can be considerable
on some types of application.
The immediate overhead is a CPU overhead of checking a block contents after each change but a secondary effect is than that this means blocks are held for longer
periods of time so other sessions needing the current block image
may have to wait longer.
The actual overhead on any system depends heavily on the application profile and data layout. If you are upgrading to Oracle8i or 9i from an earlier
release it is advisable to leave DB_BLOCK_CHECKING set to FALSE until
you have checked that there are no corruptions on the database. See Note:32969.1 The FALSE / OFF setting is provided for compatibility with earlier releases where block checking is disabled as a default. From 10.2 onwards DB_BLOCK_CHECKING can be set to FULL / MEDIUM / LOW / OFF to give finer control over the amount of checking which occurs. OFF does no checking. LOW does some basic block header checks, FULL and MEDIUM cause some varying degrees of datablock content checking. Articles: Database block checking features Note:32969.1 Overview of Init.Ora Parameter Reference notes Note:68462.1 ========================================================================
LOG_CHECKPOINT_INTERVALspecifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical operating system blocks, not database blocks.
FAST_START_MTTR_TARGETenables you to specify the number of seconds the database takes to perform crash recovery of a single instance. When specified,