So far, you have learned what the Database Writer does. You also have seen how DBWR determines which records to write to disk from the buffer cache. The next piece in this puzzle is to determine when the Database Writer does its writing. There are four conditions under which DBWR is called into action:

•  A user process writes a new dirty buffer into the database buffer cache. The user process is smart enough to detect that the number of dirty buffers has exceeded a limit the DBA has set for dirty buffers (one-half of DB_BLOCK_WRITE_BATCH, which Oracle calculates based on your database buffer size). Being a good citizen in the Oracle background process world, the user process politely informs DBWR that it is time to wake up and do some writing.

•  A user process is looking for a free buffer to write to and cannot find it. It does not scan every possible buffer in the buffer cache because this takes too much time. Instead, it searches a number of buffers specified by a parameter set by you, the DBA (DB_BLOCK_MAX_SCAN_CNT, which is calculated by Oracle based on your database buffer size). When it has looked at this many buffers and has not found a free one, the user process concludes that it is time for some writing (there are a lot of dirty buffers out there). It then signals DBWR to begin writing.

•  The Database Writer wakes itself up routinely to see whether there is any work for it (dirty database buffers). This period is set to three seconds.

•  Finally, the Log Writer or the checkpoint process needs to have those records that show the latest change number applied to the database written to each of the data files when a checkpoint occurs. Because this function is especially important for database recovery purposes, these processes get special attention and signal the Database Writer to write these records when they are ready.

How much does the Database Writer transfer to disk when it is called into action? The checkpoint records are directed for immediate write when the Database Writer is awakened for a checkpoint. For the other situations, the number of records written depends on how DBWR was awakened and certain Oracle configuration parameters.

If the Database Writer is awakened by user processes that detect that the number of dirty buffers is too high or cannot find a free buffer, DBWR goes to the list of dirty buffers and writes a batch of records. There may be times when there are not enough buffers available on the list of dirty buffers that Oracle maintains (there is a time delay between dirtying the buffer and this list being updated). If DBWR cannot find enough data on the dirty list to fill up its transfer quota, it scans the list of buffers, starting at the least recently used end, to find dirty buffers to write to disk. The Database Writer is a very service-oriented process.

If DBWR is awakened on a user process timeout waiting for a non-dirty buffer, it uses a slightly different approach to writing data buffers. It goes through the list of buffers, starting at the least recently used end, and selects dirty buffers to write to disk. It searches twice as many buffers as was specified by the DB_BLOCK_WRITE_BATCH parameter on each awakening (remember, many of these buffers will not be dirty and therefore not need writing). Any dirty buffers it finds are written to disk. Each time DBWR wakes up it searches a new set of buffers on the least recently used list of buffers. Eventually, if the database has a relatively light data entry rate, all the dirty buffers in memory will be written to disk. This is the case in most of the systems I have worked with and would probably apply to all but the busiest transaction processing systems.

Certain operating systems (UNIX, for one) enable you to activate multiple database writer processes to share the load. The number of DBWR processes you use is controlled by the DB_WRITERS parameter in the init.ora file. Oracle recommends having at least one Database Writer process for each disk drive in your system. You would not implement 135 DBWR processes for the one large information warehouse I worked on that had 135 mirrored disk drives (2GB each). I would recommend having enough DBWR processes for the expected number of disk drives that would be written to at a given time. Start with a value you consider reasonable and then increase its value and see whether your performance improves.

Now is as good a time as any to bring out one of the fundamental design concepts used by Oracle to improve performance in its processes—parallelization. Oracle enables you to assign multiple database writers to do the work when it becomes too much for one to handle. You need to understand your computer’s architecture before you jump at the chance to improve performance by adding additional processes. Parallelization techniques are useful for systems with a number of disk drives (parallel Database Writers) and computer processors (as in the parallel query option). Certain computers, such as most of the HP and IBM UNIX servers, are based around a single processor architecture. They would not benefit from the multiple query processors as much as a large multiprocessor-based computer such as the Sequent and Pyramid families. Also, computers with all their Oracle data stored on one or two disk drives would not benefit from a large number of DBWR processes. Just ensure that you understand what you are trying to do when you add parallel processes and that it will work with your computer architecture.