1

As far as I know, DBWs write the dirty blocks to the disc and unlocks the blocks on commit operation takes place. However, does it really happen as I believe DBWn doesn't directly related to commit issue?

If not, how does it perform this write operation?

Because it is also known as "lazy-writer".

I'm confused.

general46
  • 800
  • 6
  • 16
  • 1
    I'm not sure I understand the question being asked. Dirty blocks do not need to be written to disk during a commit. Redo information required to make the changes to those blocks does need to be written as part of the commit. The DBW process writes dirty blocks to disk but that happens completely asynchronously to transactions-- dirty blocks might be written long before or long after a transaction commits. And there is nothing to "unlock" about a dirty block. I'm not sure if you're asking about what DBW is doing, what a commit operation is doing, or something else. – Justin Cave Oct 24 '19 at 19:51

1 Answers1

0

Say you are updating a record in a table. At a very high abstract level, the following steps happen once the block that holds the target record is identified in (or copied to) the database buffer cache.

  1. A REDO Change Vector describing how to insert an UNDO record into an UNDO block is created. This UNDO is needed for rolling back the changes if needed in the future. The UNDO may also be needed to satisfy SQLs that started before the update was initiated and are still running.
  2. A REDO Change Vector describing the data block change to perform the requested update is created.
  3. Combining the two REDO Change Vectors, a REDO record is created and written to the circular Redo Log Buffer (RLB).
  4. The UNDO record is inserted into the UNDO block in the buffer cache.
  5. The actual change is made in the data block in the buffer cache.

  6. At this point the RLB may contain REDO entries from multiple Server Processes. A portion of the RLB is sequentially written by the single Log Writer process (LGWR) to the online Redo log files. Only if one of the following occurs, all the Redo Entries from RLB are written to the online Redo log files.

    a. One of the Server Processes issues a COMMIT. (log file sync wait event).

    b. A log switch occurs (An online redo log file got filled and LGWR switches to a new online redo log file from the limited number of online redo log files into which LGWR writes in round-robin fashion).

    c. Every 3 seconds.

    d. The RLB is one-third full.

    e. When the Database Writer process (DBWn) needs to write dirty buffers to disk. (When this need will arise is explained later).

  7. Pleae note that even now the dirty buffers (whcih inlcudes the block which we modified in the buffer cache) has not been written to the disc (data file). Now let us talk about DBWn.

  8. Unlike LGWR process there can be multiple DBWn processes (denoted by the suffix n) which can asynchronously (free from when the actual change was written to the data block in buffer cache, of course after the change was made to the buffer) write the dirty buffers from the database buffer cache to the disk in parallel writes. But these writes happen only under the following circumstances.

    a. When there are no more clean buffers in database buffer cache

    b. When a checkpoint occurs. (Checkpoint is the point in the redo log file (System Change Number(SCN)) upto which the dirty buffers are already written to the disk (i.e) Upto this point in the redo log file the data file is in synch with the buffer cache.)

Hence it is claimed that DBWn performs lazy-writes only when it is needed as against the fast near to real time writes performed by LGWRs.

One more thing, Oracle's granular logical write unit is Block. On the disk in the datafile this block is identiifed and then copied to the buffer cache. Oracle never locks blocks. It obtains a latch on the buffer block's header before reading the buffer. This is why we see "latch cache buffer chains" wait events when some huge range scan prevents other processes from obtaining this latch. As far as locking is considered when we update a row in an Oracle block, Oracle locks at granular level, the row that we update.

The memory and process architecture under Oracle instance architecture in Oracle documentation is a good place to understand these concepts.

ArtBajji
  • 949
  • 6
  • 14