DBWn, the Database Writer in Software

Creating Code39 in Software DBWn, the Database Writer

Always remember that sessions do not as a general rule write to disk They write data (or changes to existing data) to buffers in the database buffer cache It is the database writer that subsequently writes the buffers to disk It is possible for an instance to have several database writers (up to a maximum of twenty), which will be called DBW0, DBW1, and so on: hence the use of the term DBWn to refer to the database writer The default is one database writer per eight CPUs, rounded up TIP How many database writers do you need The default number may well be correct Adding more may help performance, but usually you should look at tuning memory first As a rule, before you optimize disk I/O, ask why there is any need for disk I/O DBWn writes dirty buffers from the database buffer cache to the datafiles but it does not write the buffers as they become dirty On the contrary: it writes as few buffers as possible The general idea is that disk I/O is bad for performance, so don t do it unless it really is needed If a block in a buffer has been written to by a session, there is a reasonable possibility that it will be written to again by that session, or a different one Why write the buffer to disk, if it may well be dirtied again in the near future The algorithm DBWn uses to select dirty buffers for writing to disk (which will clean them) will select only buffers that have not been recently used So if a buffer is very busy, because sessions are repeatedly reading or writing it, DBWn will not write it to disk There could be hundreds or thousands of writes to a buffer before DBWn cleans it It could be that in a buffer cache of a million buffers, a hundred thousand of them are dirty but DBWn might only write a few hundred of them to disk at a time These will be the few hundred that no session has been interested in for some time DBWn writes according to a very lazy algorithm: as little as possible, as rarely as possible There are four circumstances that will cause DBWn to write: no free buffers, too many dirty buffers, a three-second timeout, and when there is a checkpoint

EXAM TIP What will cause DBWR to write No free buffers, too many dirty buffers, a three-second timeout, or a checkpoint First, when there are no free buffers If a server process needs to copy a block into the database buffer cache, it must find a free buffer A free buffer is a buffer that is neither dirty (updated, and not yet written back to disk) nor pinned (a pinned buffer is one that is being used by another session at that very moment) A dirty buffer must not be overwritten because if it were changed, data would be lost, and a pinned buffer cannot be overwritten because the operating system s memory protection mechanisms will not permit this If a server process takes too long (this length of time is internally determined by Oracle) to find a free buffer, it signals the DBWn to write some dirty buffers to disk Once this is done, these will be clean, free, and available for use Second, there may be too many dirty buffers too many being another internal threshold No one server process may have had a problem finding a free buffer, but overall, there could be a large number of dirty buffers: this will cause DBWn to write some of them to disk Third, there is a three-second timeout: every three seconds, DBWn will clean a few buffers In practice, this event may not be significant in a production system because the two previously described circumstances will be forcing the writes, but the timeout does mean that even if the system is idle, the database buffer cache will eventually be cleaned Fourth, there may be a checkpoint requested The three reasons already given will cause DBWn to write a limited number of dirty buffers to the datafiles When a checkpoint occurs, all dirty buffers are written This could mean hundreds of thousands of them During a checkpoint, disk I/O rates may hit the roof, CPU usage may go to 100 percent, end user sessions may experience degraded performance, and people may start complaining Then when the checkpoint is complete (which may take several minutes), performance will return to normal So why have checkpoints The short answer is, don t have them unless you have to EXAM TIP What does DBWn do when a transaction is committed It does absolutely nothing The only moment when a checkpoint is absolutely necessary is as the database is closed and the instance is shut down a full description of this sequence is given in 3 A checkpoint writes all dirty buffers to disk: this synchronizes the buffer cache with the datafiles, the instance with the database During normal operation, the datafiles are always out of date, as they may be missing changes (committed and uncommitted) This does not matter, because the copies of blocks in the buffer cache are up to date, and it is these that the sessions work on But on shutdown, it is necessary to write everything to disk Automatic checkpoints only occur on shutdown, but a checkpoint can be forced at any time with this statement:

alter system checkpoint;