TheDataGirl

The Database Writer makes use of the OS processes, DBWn and BWnn. The database writer uses a background process which is responsible for receiving blocks of data from the SGA’s buffer cache and writing to disk. The diagram below shows the architecture of Oracle 12c database. We will be seeing this diagram in other articles where we will discuss the components which make up the Oracle Database architecture. (Thomas, 2014)

There are several points at which the Database Writer writes to disk, one of them being when there are no free buffers. To understand this, we first need to understand the scope of the database buffer cache within the Oracle database architecture. The Database Buffer cache lives within the SGA is shared amongst all users on the database and contains data caches of the latest blocks of data which have been accessed. Further to diagram 1, diagram 2 goes into more detail on the Database writer process.

There are three different types of buffers which exist; dirty buffers, free buffers, and pinned buffers. Free buffers are buffers which are empty or marked to be overwritten. If these become full, Oracle must take it upon itself to write to disk so that some buffers are freed up (marked to be overwritten).
Another trigger for the Database Writer to write to disk is if the threshold for the number of dirty buffers has been reached. Dirty buffers are buffers which have modified data which are yet to be written to disk. (Swing, 2012) As soon as buffers are modified, they are immediately marked as dirty. The Database Writer process writes these dirty buffers to disk, freeing up available buffers for writing purposes. (Oracle)
For the sake of completeness, we will mention pinned buffers. Pinned buffers are buffers which are currently in use (being accessed). When the user is no longer in need of these buffers, these buffers are freed up. There are some cases where these are kept in the pinned buffer due to an expectation of making use of the data. (Thomas, 2014)
In order to maximize performance, Oracle makes use of the least recently used algorithm (LRU) which checks the data blocks which are most frequently used. It stores the buffers in which the data blocks belong to in memory for easy and quick access. (Thomas, 2014)
The Database writer writes to the data files every second, when the data buffer cache is of 75% capacity when a commit has been executed when the log writer has been called, immediately before the showdown process begins. (Database Writer Process (DBWn), 2016)
When there are not enough free buffers, due to them being pinned or dirty, the Oracle database will experience free buffer waits. The definition for this event is self-explanatory. The free buffer waits event waits for buffers to be freed up (which happens when the database writer writes buffers to disk).
There are certain situations where the Database Writer might not be keeping up with writing buffers to disk. These include if the I/O is performing at a sub-optimal speed. Other problems which may occur are if the database writer is waiting for resources (e.g. latches) or if the buffer size is too small. In this case, the performance of the entire Oracle database will suffer as a result. (Oracle, Free buffer waits (%))

References
Database Writer Process (DBWn). (2016). Retrieved from OracleORA: https://oracleora.wordpress.com/database-writer-process-dbwn/
Oracle. (n.d.). DBWR Checkpoints (per second). Retrieved from Docs.Oracle: https://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_instance_throughput_dbwrcheckpoints_ps.html
Oracle. (n.d.). Free buffer waits (%). Retrieved from docs.oracle: https://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_wait_bottlenecks_free_buffer_waits_pct.html
Swing, M. (2012, September 21). 11g Database OCA Cheat Sheet Notes. Retrieved from blog.trutek: http://blog.trutek.com/11g-database-oca-cheat-sheet-notes/
Thomas, B. (2014). OCA Oracle Database 12c Administrator Certified Associate STUDY GUIDE. Indianapolis, Indiana: John Wiley & Sons, Inc.