Database Writer Process (DBW)

The database writer process (DBW) writes the contents of database buffers to data files. DBW processes write modified buffers in the database buffer cache to disk.

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

The DBW 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 DBW to write. DBW writes dirty buffers to disk asynchronously if possible while performing other processing.
  • DBW periodically writes buffers to advance the checkpoint, which is the position in the redo thread from which instance recovery begins. The log position of the checkpoint is determined by the oldest dirty buffer in the buffer cache.

 

By default there is 1 database writer process available for each database :

oracle@localhost oradata]$ ps -ef|grep dbw
oracle 6250 1 0 21:59 ? 00:00:00 ora_dbw0_test
oracle 6387 6179 0 21:59 pts/2 00:00:00 grep dbw

We can increase no of writer process using db_writer_processes parameter .

SQL> show parameter db_writer

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

we can see it have default value 1. Now , lets change it to 3

SQL> alter system set db_writer_processes=3;
alter system set db_writer_processes=3
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set db_writer_processes=3 scope=spfile;

System altered.

SQL>

It’s a static parameter and database must be restarted to take effect of this parameter.

SQL> startup force
ORACLE instance started.

Total System Global Area 1419685888 bytes
Fixed Size 2288344 bytes
Variable Size 905970984 bytes
Database Buffers 503316480 bytes
Redo Buffers 8110080 bytes
Database mounted.
Database opened.
SQL> show parameter db_writer

NAME                TYPE     VALUE
------------------- -------- -------
db_writer_processes integer  3
SQL>

This is a test database so I can use startup force else we need to gracefully shut down database and start it.

[oracle@localhost oradata]$ ps -ef| grep dbw
oracle 6937 1 0 22:10 ? 00:00:00 ora_dbw0_test
oracle 6939 1 0 22:10 ? 00:00:00 ora_dbw1_test
oracle 6941 1 0 22:10 ? 00:00:00 ora_dbw2_test
oracle 7084 6179 0 22:10 pts/2 00:00:00 grep dbw
[oracle@localhost oradata]$

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.