Instance Architecture   «Prev  Next»

Lesson 8 Database Writer (DBW0)
Objective Identify the purpose of the DBW0 process.

(DBW0) Database Writer

The Database Writer (DBW0) process has one purpose in life: to write modified data back to the datafiles.

Changed data in dirty buffers

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:

1) When a database first starts, all the database buffers are empty.
1) When a database first starts, all the database buffers are empty.

2) As queries are executed, the cache begins to fill up. Blocks are read from disk and placed in the buffers.
2) As queries are executed, the cache begins to fill up. Blocks are read from disk and placed in the buffers.

3) UPDATE, INSERT, and DELETE statements cause the data in some blocks to change.
3) UPDATE, INSERT, and DELETE statements cause the data in some blocks to change.

4) The database writer periodically checks for modified blocks and writes them back to disk.
4) The database writer periodically checks for modified blocks and writes them back to disk.

5) Modified blocks are written to disk, but also kept in memory in case they are needed again.
5) Modified blocks are written to disk, but also kept in memory in case they are needed again.

6) If database activity slows, the database writer will catch up. Here the buffers are full but all changes have been written.
6) If database activity slows, the database writer will catch up. Here the buffers are full but all changes have been written.

  1. When a database first starts, all the database buffers are empty
  2. As queries are executed, the cache begins to fill up. Blocks are read from disk and placed in the buffers.
  3. UPDATE, INSERT, and DELETE statements cause the data in some blocks to change.
  4. The database writer periodically checks for modified blocks and writes them back to disk.
  5. Modified blocks are written to disk, but also kept in memory in case they are needed again.
  6. If database activity slows, the database writer will catch up.



Oracle RAM Memory Monitoring

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.


Multiple Database Writers

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.

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

SEMrush Software