Archive

Archive for the ‘DB_WRITER_PROCESSES’ Category

DB_WRITER_PROCESSES

September 20, 2010 Leave a comment

Setting the DB_WRITER_PROCESSES parameter (Background process DBWR) to a value greater than one is supported starting with the Oracle 8.0 release.

Multiple DBWR processes are mainly used to simulate asynchronous I/O when the operating system does not support it.  Since Windows NT and Windows 2000 use asynchronous I/O by default, using multiple DBWR processes may not necessarily improve performance.  Increasing this parameter is also likely to have minimal effect on single-CPU systems.  Increasing this parameter could, in fact, reduce performance on systems where the CPU’s are already over burdened.  In cases where the main performance bottleneck is that a single DBWR process cannot keep up with the work load, then increasing the value for DB_WRITER_PROCESSES may improve performance.

When increasing DB_WRITER_PROCESSES it may also be necessary to increase the DB_BLOCK_LRU_LATCHES parameter, as each DBWR process requires an LRU latch.

How to change db_writer_processes parameter

SQL> show parameter db_writer_processes
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
db_writer_processes                  integer     1

SQL> alter system set db_writer_processes=2 scope=spfile sid='*';

System altered.

Now restart the database to take the change effect.

 
SQL> show parameter db_writer_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
db_writer_processes                  integer     2

“The number of DBWR should be two times the number of CPUs (vary 1 or 2 depending on idle state of CPUs during production/test run)”

DB_WRITER_PROCESSES = 2 * number of CPU

Default value of DB_WRITER_PROCESSES is 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.

Range of value in Oracle 10g- 1 to 20 but in Oracle 11g – 1 to 36.

Follow

Get every new post delivered to your Inbox.

Join 69 other followers