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
