Oracle Database

  • The DBWn process writes dirty buffer to disk under the following conditions:
    • When a checkpoint is issued.
    • When a server process cannot find a clean reusable buffer after scanning a threshold number of 
    • buffers.
    • Every 3 seconds
  • Used Internal Structures on Db writer Cache Buffer Management :
    • Cache Buffer Chains
      • doubly-linked hash lists, there are buffers headers(each buffer header describes the properties of one buffer - 1-1 relationship) in this hash list. # of headers = # of buffers (db_block_buffer)
      • Chains are managed by cache buffer chain latch. Foreground processes acquire this latch to search the chain list.
      • Example of the contents of a buffer header;  Buffer status (st) : xcurrent, CR, reading.   Lock mode (md) : exclusive (excl), shared (shr) , and null (null)   In cache data block summary : dba, inc & seq, version and block type   Buffer holder and waiters list   Reference to the buffer’s location on the hash chain, a pointer location of the buffer header on either the LRU or LRUW list and the pointer to the actual buffer block (ba). 
    • LRU list 
        • Managed under Least Recently Used algorithm. New block are placed on the head of the list(exception : Blocks coming from FTS(excluding small tables )) are placed on the end of the list and there is a limit for them= db_block_multi_read_count). The end of this list contains the buffers that have not been referenced recently. Foregroung processes searches the LRU list by beginning from the end.
        • Status of the buffers in LRU list (free--> unused buffers, pinned (pinned clean/pinned dirty)--> held by a user and/or have waiters against them, dirty-->modified buffers(modified and not moved to LRUW yet) )
    • LRUW list (dirty list) --> dirty buffer list (blocks  can be on LRUW or LRU list , cant be both)
        • Contains dirty buffers, that are to be written to Disk by DBWR.
        • DBWR writes dirty buffers to disk when signalled by the write buffers when idle, checkpoint write , write dirty find clean etc.. events.
  • DBWR 's function:
        • SCENARIO 1: The function of DBWR is writing buffers to disk. When a foreground process reads a block from disk, it must find a free buffer to store its read content. Foreground process acquire the relevant latch and sarch the LRU starting from the end. When the foreground process finds an available buffer , it will use it. At the same time, through its search if foreground process finds dirty buffers, it will move this dirty buffers to LRUW list. This searching will last till _db_block_max_scan_count is reached. When this limit is reached, dbwr is signalled. With this signal, DBWR will start to perform a large batch to make the dirty buffers to be clean. On the other hand, if LRUW list is full , and free buffer cant be found on LRU list, Dbwr will be in the panic state and will be fully focused  on cleaning up the LRU and LRUW lists.
        • SCENARIO 2 : Dbwr writes dirty buffers when it s idle too.(without any signal). Idle timeout of Dbwr is 3 seconds. In every 3 seconds, if not signalled, Dbwr travers through the buffer headers to write any current or dirty blocks.
        • SCENARIO 3: When checkpoint occurs ,CKPT signals DBWR with a buffer header array(checkpoint flag is set on these headers). The buffers written because of checkpoint is not considered as free. (for a better cache hit ratioSCENARIO 4 : While making the buffers dirty, an internal variable is incremented, if this variable will reach the threshold, DBWR will be signalled to flush drity buffers to disk. This is a efficient management method for the LRU -LRUW list.
      • In general, when DBWR is signaled to clean-out the dirty list, it will always gather _db_block_write_batch full of buffers from the LRUW list, pinning buffers its going to write along the way.
      • Note that LRUW is populated by foreground processes as well as DBWR. If LRUW is filled up by foreground processes, we can say that DBWR is not keeping up..

It was acutally prepared for developers.

The toad presentation is in Turkish. It present toad generally. The details can be given (as I will) during the presentation by using Toad tool directly.
The presentation is focused on Toad for Oracle version 10.6 with admin and sql optimizer modules.

No comments :

Post a Comment

If you will ask a question, please don't comment here..

For your questions, please create an issue into my forum.

Forum Link:

Register and create an issue in the related category.
I will support you from there.