【轉載】oracle的io優化--db_writer_processes & dbwr_io_slaves對比

time：2011/09/29

db_writer_processes 和 dbwr_io_slaves對比

 

在計算機世界裡，磁碟的發展速度遠低於cpu，memory。磁碟io現在已經成為計算機的瓶頸，對於oracle的db系統。磁碟io更是
重要。在oracle裡為了提高io的速度，常用引數db_writer_processes 和 dbwr_io_slaves

在資料庫裡事務數非常高，或db cache很大，一個DBWn process跟不上資料的load，我們可以調整這兩個引數，這兩個
引數的作用就是增加io讀寫程式，啟用非同步io，加快io的速度

多個 slaves 可以並行寫資料檔案，而多個dbwr也可以並行寫資料檔案
一個 dbwr 多個 slaves是 dbwr 蒐集dirty  buffer 而 slaves 寫資料檔案
多個dbwr 可以並行地蒐集dirty buffer 並且並行地寫資料檔案

但是如果系統支援AIO一般不用設定多dbwr 或者 io slaves

db_writer_processes：在多cpu，多磁碟的環境，一般是每8個cpu一個dbwr程式

 

什麼時候配置這兩個引數
如果系統的io是瓶頸是，檢查os是否支援非同步io，如果支援非同步io，但目前沒有使用，那就啟用非同步io來緩解io瓶頸。如果os不支援非同步io，
或os已經啟用非同步io，但io還是瓶頸，那我們可以配置多個dbwr程式。配置這兩個引數是要注意，大都是如下

一個db_writer_processes，多個dbwr_io_slaves
多個db_writer_processes，dbwr_io_slaves不啟用

 

官網如下：http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/instance_tune.htm

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

-------end--------

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15747463/viewspace-758004/，如需轉載，請註明出處，否則將追究法律責任。