DBWn 进程和 DB_WRITER_PROCESSES/DBWR_IO_SLAVES 参数

1.DBWn进程 Database Writer Process (DBWn)

    The database writer process (DBWn) writes the contents of database buffers to data files. DBWn processes write modified buffers in the database buffer cache to disk (see "Database Buffer Cache").

    Although one database writer process (DBW0) is adequate for most systems, you can configure additional processes—DBW1 through DBW9 and DBWa through DBWj—to improve write performance if your system modifies data heavily. These additional DBWn processes are not useful on uniprocessor systems.

    The DBWn process writes dirty buffers to disk under the following conditions:

    When a server process cannot find a clean reusable buffer after scanning a threshold number of buffers, it signals DBWn to write. DBWn writes dirty buffers to disk asynchronously if possible while performing other processing.

    DBWn periodically writes buffers to advance the checkpoint, which is the position in the redo thread from which instance recovery begins (see "Overview of Checkpoints"). The log position of the checkpoint is determined by the oldest dirty buffer in the buffer cache.

    In many cases the blocks that DBWn writes are scattered throughout the disk. Thus, the writes tend to be slower than the sequential writes performed by LGWR. DBWn performs multiblock writes when possible to improve efficiency. The number of blocks written in a multiblock write varies by operating system.

    数据库写入器进程 （DBWn） 将数据库缓冲区的内容写入到数据文件中。DBWn 进程将数据库缓存中被修改的缓冲区写入到磁盘中（请参见"数据库缓冲区高速缓存"）。

    虽然一个数据库写入进程 （DBW0） 对于大多数系统来说已经足够，但如果您的系统大量修改数据，您也可以配置额外的写入器进程——从 DBW1到 DBW9，和从 DBWa 到 DBWj ——来提高写入性能。这些额外的 DBWn 进程在单处理器系统上用处不大。

    DBWn 进程在下列条件下会将脏缓冲区写入到磁盘： 

    当服务器进程扫描了额定数目的缓冲区后，仍未找到干净的可重复使用的缓冲区时，它会通知 DBWn 执行写入操作。DBWn 尽可能以异步方式将脏缓冲区写入到磁盘，以便同时能执行其他处理。

    DBWn 周期性地写出缓冲区，以推进检查点，该点是重做线程中实例恢复开始的位置（请参阅"检查点概述"）。检查点的日志位置由在缓冲区高速缓存中最老的脏缓冲区确定。

    在许多情况下，DBWn 写入的块将遍布整个磁盘。因此，该写操作往往要比由 LGWR 执行的顺序写入速度慢。DBWn 进程尽可能进行多块写入，以提高效率。多块写入的数据块数量因操作系统而异。

2.DB_WRITER_PROCESSES参数

    1 or CPU_COUNT / 8, whichever is greater. If the number of processor groups is less than 36 but greater than the number of DB writer processes, then the number of DB writer processes is adjusted to be a multiple of the number of processor groups. If the number of DB writer processes is greater than or equal to the number of processor groups, then there is no adjustment.

    DB_WRITER_PROCESSES specifies the initial number of database writer processes (DBW0, ... DBW9 and DBWa, ... DBWz) for an instance. This parameter is useful for systems that modify data heavily.

    1或者是参数CPU_COUNT/8，或者是更大。如果处理器组的数量小于36但是大于DB写进程的数量，那么DB写进程的数量需要调整到处理器组的数量，如果DB写进程的数量大于或者等于处理器组的数量，那么就无需调整。

    DB_WRITER_PROCESSES指定了数据库实例中写进程数量(DBW0,..DBW9和DBWa,..DBWz)的初始值。这个参数对有大量数据修改的系统是很有用的。

3.DBWR_IO_SLAVES

    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参数仅仅和数据的写进程(DBW0)有所关系。它指定了DBW0进程可以使用的服务器IO进程数量。DBW0和它的服务器进程经常写数据到磁盘。默认情况下，这个值是0，也就是说IO服务进程不被使用。

    如果你设置了DBWR_IO_SLAVES是一个非零值，那么被ARCH和LGWR进程使用的IO服务进程被设置为4。然而Recovery Manager使用的IO服务进程被设置为4，仅仅当异步IO开启的情况下(无论是你的操作系统不支持异步IO或者disk_asynch_io被设置为false)

    典型的说，IO服务进程被用来在哪些不支持异步IO或者是无法完全实施异步IO的平台上模拟异步IO。然而，你可以使用IO服务进程甚至当异步IO在被使用的情况下。在这种情况下IO服务进程会使用异步IO。IO服务进程在那些有非常大的IO吞吐，或者是异步IO开启的数据环境中是有作用的。

4.调优说明

10.3.8.3 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.

10.3.8.3.1 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 Database either selects an appropriate default setting for DB_WRITER_PROCESSES or adjusts a user-specified setting.

10.3.8.3.2 DBWR_IO_SLAVES

If it is not practical to use multiple DBWR processes, then Oracle Database 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.

当你的设备仅有一个cpu的时候，无法调整DB_WRITER_PROCESSES参数，而又 需要增大IO可以考虑调整DBWR_IO_SLAVES参数

那么我们如何来判断目前的DBWR是否足够呢？STATSPACK报告或者AWR报告是十分好的工具。

                                                            Free Writ     Buffer

     Number of Pool         Buffer     Physical    Physical Buff Comp       Busy

P      Buffers Hit%           Gets        Reads      Writes Wait Wait      Waits

--- ---------- ---- -------------- ------------ ----------- ---- ---- ----------

D    4,405,998   98    945,225,805   21,899,493   2,291,122    0    0  5,692,101

如果经常会有write complete waits存在，那么就说明DBWR的写性能不足，可以考虑通过增加DBWR来改善脏块写的性能。另外，经常出现free buffer waits的问题，如果我们无法通过扩大DB CACHE来改善DB CACHE的性能，那么增加DBWR的数量，也可以从另外一个侧面改善这方面的问题。