Tuesday, November 2, 2010

On DB_BLOCK_CHECKING,DB_WRITER_PROCESSES and Checkpointing (Ref:Metalink)

This parameter was introduced in Oracle 8.0.4.

  DB_WRITER_PROCESSES cannot be greater than CPU COUNT.
  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 

  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 
  parallelised   by 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 

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.

In 10g:

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
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
Disable fast-start checkpointing only when system I/O capacity is insufficient
with fast-start
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
requested MTTR.
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 
  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 
   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 
    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. 

     Database block checking features                  Note:32969.1
     Overview of Init.Ora Parameter Reference notes    Note:68462.1

LOG_CHECKPOINT_INTERVAL specifies 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_TARGET enables you to specify the number of
 seconds the database takes to perform crash recovery of a single 
instance. When specified, FAST_START_MTTR_TARGET is overridden 

No comments:

Post a Comment