db_writer_processes DBWR_IO_SLAVES

 

DB_WRITER_PROCESSES

Parameter type Integer
Default value 1
Parameter class Static
Range of values 1 to 20

DB_WRITER_PROCESSES is useful for systems that modify data heavily. It specifies the initial number of database writer processes for an instance.

Consider Multiple Database Writer (DBWR) Processes or I/O Slaves
Configuring multiple database writer processes, or using I/O slaves, is useful when
the transaction rates are high or when the buffer cache size is so large that a single
DBWn process cannot keep up with the load.
DB_WRITER_PROCESSES The DB_WRITER_PROCESSES initialization parameter
lets you configure multiple database writer processes (from DBW0 to DBW9 and
from DBWa to DBWj). Configuring multiple DBWR processes distributes the work
required to identify buffers to be written, and it also distributes the I/O load over
these processes. Multiple db writer processes are highly recommended for systems
with multiple CPUs (at least one db writer for every 8 CPUs) or multiple processor
groups (at least as many db writers as processor groups).
Based upon the number of CPUs and the number of processor groups, Oracle either
selects an appropriate default setting for DB_WRITER_PROCESSES or adjusts a
user-specified setting.

• DBWR_IO_SLAVES
• Parameter type Integer
• Default value 0
• Parameter class Static
• Range of values 0 to operating system-dependent

  DBWR_IO_SLAVES is relevant only on systems with only one database writer
  process (DBW0). It specifies the number of I/O server processes used by the DBW0
  process. The DBW0 process and its server processes always write to disk. By default,
  the value is 0 and I/O server processes are not used.
  If you set DBWR_IO_SLAVES to a nonzero value, the number of I/O server
  processes used by the ARCH and LGWR processes is set to 4. However, the number of
  I/O server processes used by Recovery Manager is set to 4 only if asynchronous
  I/O is disabled (either your platform does not support asynchronous I/O or disk_
  asynch_io is set to false.
  Typically, I/O server processes are used to simulate asynchronous I/O on platforms
  that do not support asynchronous I/O or that implement it inefficiently. However,
  you can use I/O server processes even when asynchronous I/O is being used. In
  that case the I/O server processes will use asynchronous I/O.
  I/O server processes are also useful in database environments with very large I/O
  throughput, even if asynchronous I/O is enabled.

DBWR_IO_SLAVES If it is not practical to use multiple DBWR processes, then Oracle
provides a facility whereby the I/O load can be distributed over multiple slave
processes. The DBWR process is the only process that scans the buffer cache LRU
list for blocks to be written out. However, the I/O for those blocks is performed by
the I/O slaves. The number of I/O slaves is determined by the parameter DBWR_
IO_SLAVES.
DBWR_IO_SLAVES is intended for scenarios where you cannot use multiple DB_
WRITER_PROCESSES (for example, where you have a single CPU). I/O slaves are
also useful when asynchronous I/O is not available, because the multiple I/O
slaves simulate nonblocking, asynchronous requests by freeing DBWR to continue
identifying blocks in the cache to be written. Asynchronous I/O at the operating
system level, if you have it, is generally preferred.
DBWR I/O slaves are allocated immediately following database open when the first
I/O request is made. The DBWR continues to perform all of the DBWR-related
work, apart from performing I/O. I/O slaves simply perform the I/O on behalf of
DBWR. The writing of the batch is parallelized between the I/O slaves.
• Note: Implementing DBWR_IO_SLAVES requires that extra shared
  memory be allocated for I/O buffers and request queues. Multiple
  DBWR processes cannot be used with I/O slaves. Configuring I/O
  slaves forces only one DBWR process to start.

Choosing Between Multiple DBWR Processes and I/O Slaves Configuring multiple DBWR
processes benefits performance when a single DBWR process is unable to keep up
with the required workload. However, before configuring multiple DBWR
processes, check whether asynchronous I/O is available and configured on the
system. If the system supports asynchronous I/O but it is not currently used, then
enable asynchronous I/O to see if this alleviates the problem. If the system does not
support asynchronous I/O, or if asynchronous I/O is already configured and there
is still a DBWR bottleneck, then configure multiple DBWR processes.
• Note: If asynchronous I/O is not available on your platform, then
  asynchronous I/O can be disabled by setting the DISK_ASYNCH_
  IO initialization parameter to FALSE.

  Using multiple DBWRs parallelizes the gathering and writing of buffers. Therefore,
  multiple DBWn processes should deliver more throughput than one DBWR process
  with the same number of I/O slaves. For this reason, the use of I/O slaves has been
  deprecated in favor of multiple DBWR processes. I/O slaves should only be used if
  multiple DBWR processes cannot be configured.

从以上这些官方文档可以看出：如果系统存在dbwr写出性能方面的瓶颈，按以下顺序来调整：

1.首先需要考虑系统是否能开启异步I/O以及是否已经开启异步I/O，如果没有开启先开启。
2.如果不支持异步I/O，或者已经开启，或者开启之后没有缓解，那么考虑设置db_writer_processes参数。设置原则是：
• 如果系统只有一个CPU，则不能设置db_writer_processes大于一，此时使用DBWR_IO_SLAVES参数
• 如果系统有多个CPU，db_writer_processes=num(cpu)/8
• 设置DBWR_IO_SLAVES的话，则只能强制设置一个db_writer_processes

原文地址：http://www.dbasky.com/oracle/db_writer_processes.html