(DBW0) Database Writer

The Database Writer (DBWn) process still exists in Oracle 19c as a part of the instance architecture. Its primary purpose remains the same: to write modified (dirty) database buffers from the buffer cache in the System Global Area (SGA) to the datafiles on disk. This is essential for ensuring that the database maintains consistency and durability while also freeing up buffer cache space for new operations.
Key Points About DBWn in Oracle 19c:

1. DBWn Naming:
   • In Oracle 19c, the Database Writer process can still have multiple instances, labeled as DBW0, DBW1, etc., up to DBW9. The number of DBWn processes depends on the workload and system configuration.
2. Enhanced Efficiency:
   • In Oracle 19c, like its predecessors, the DBWn process works with features like Automatic Memory Management (AMM) or Automatic Shared Memory Management (ASMM) to dynamically manage memory usage.
   • Oracle has optimized background processes for better performance in cloud-enabled and hybrid environments, but the fundamental role of DBWn remains the same.
3. Coordination with Checkpointing:
   • DBWn works closely with the Checkpoint (CKPT) process to ensure that data consistency is maintained. When a checkpoint occurs, CKPT signals DBWn to flush dirty buffers to disk.
4. Cloud Optimization:
   • In Oracle 19c and other cloud-enabled versions (12c onward), DBWn has been optimized for better performance in distributed systems and Oracle Autonomous Database environments.

The DBWn process is a critical component for managing physical I/O operations, ensuring durability, and contributing to Oracle’s ACID compliance principles. Despite architectural advancements in Oracle 19c, DBWn continues to perform its essential function.
The Database Writer (DBW0) process has one purpose in life: to write modified data back to the datafiles.

Oracle holds the most recently read data from disk in a memory area known as the database buffer cache. Whenever any of that data is changed, the buffers with the changed data are marked as dirty. The database writer will periodically check for these dirty buffers, and write them back to the datafiles. The following SlideShow illustrates this process:

RAM memory monitoring is implemented in Oracle by enhancing the v$process view. The new columns in the v$process view allow you to show details about the (PGA) program global area regions for all current Oracle processes. The PGA is a dedicated area of RAM memory used by individual processes to perform RAM intensive functions, such as sorting. The three new columns in the v$process view include

1. pga_used_memory,
2. pga_allocated_memory, and
3. pga_max_memory.

After analyzing these metrics, you can

1. see the actual RAM utilization for individual background processes within the Oracle environment and
2. also look at the RAM demands of individual connections to the database.

To illustrate, consider the following query:

col c1 heading 'Program|Name'         format a30
col c2 heading 'PGA|Used|Memory'      format 999,999,999
col c3 heading 'PGA|Allocated|Memory' format 999,999,999
col c4 heading 'PGA|Maximum|Memory'   format 999,999,999

 select
    program       c1,
    pga_used_mem  c2,
    pga_alloc_mem c3,
    pga_max_mem   c4
 from
    v$process
 order by
    c4 desc;

Figure 1

PROGRAM                   PGA_USED_MEM   PGA_ALLOC_MEM   PGA_MAX_MEM
 ------------------------- ------------   -------------   -----------
 oracle@john (PMON)            120,463         234,291       234,291
 oracle@john (DBW0)          1,307,179      1,817,295     1,817,295
 oracle@john (LGWR)          4,343,655       4,849,203     4,849,203
 oracle@john (CKPT)            194,999         332,583       332,583
 oracle@john (SMON)            179,923         775,311       775,323
 oracle@john (RECO)           129,719         242,803       242,803
 oracle@john (TNS V1-V3)     1,400,543       1,540,627     1,540,915
 oracle@john (P000)            299,599         373,791       635,959
 oracle@john (P001)            299,599         373,791       636,007
 oracle@john (TNS V1-V3)     1,400,543       1,540,627     1,540,915
 oracle@john (TNS V1-V3)        22,341       1,716,253     3,625,241

This example provides insight into the behavior of the Oracle database engine. One can see that the (LGWR) log writer process is the highest consumer of PGA RAM memory, which makes sense because the Oracle Log Writer process must transfer redo log images from the Log Buffer (in RAM memory) to the online redo log filesystem. You can also see high RAM memory utilization for the (DBW0) Database Writer process. The asynchronous I/O processes used by Oracle make extensive use of RAM memory resources to ensure that all database changes are successfully written to the database. An instance may have up to ten database writer processes.

Beginning with the release of Oracle, an instance may have up to ten database writer processes. If your database experiences a very high level of update activity, multiple database writers may be necessary to keep up with the changes. The number of database writer processes is controlled by an initialization file parameter that is read at instance startup. The parameter name is DB_WRITER_PROCESSES. To configure an instance for five database writer processes, for example, you would place the following line in your initialization file:

db_writer_processes=5

Using this parameter, when the instance starts, you would have five database writer named

DBW0, DBW1, DBW2, DBW3, and DBW4.

Multiple database writers really only make sense in a multiple CPU system, because they can spread the work of writing to the datafiles evenly across the CPUs. If you are running on a single-CPU machine, multiple database writers will not give you any advantage. Pior to the release of Oracle, an instance could have only one database writer. Because there was only one writer, there was no need for a digit in the name, and the abbreviation DBWR was used. You will still see references in some Oracle related books to DBWR.

• Database Writer Slaves: If it is not practical to use multiple database writer processes, such as when the computer has one CPU, then the database can distribute I/O over multiple slave processes. DBWR is the only process that scans the buffer cache LRU list for blocks to be written to disk. However, I/O slaves perform the I/O for these blocks.
• If an Oracle user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list^[1] and signals the DBW0 background process to write some of the dirty buffers to disk.

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

[1]LRU List: When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache. You can control this default behavior of blocks involved in table scans on a table-by-table basis. To specify that blocks of the table are to be placed at the MRU end of the list during a full table scan, use the CACHE clause when creating or altering a table or cluster. You can specify this behavior for small lookup tables or large static historical tables to avoid I/O on subsequent accesses of the table.