Oracle Database

About DB WRITER:

• 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..

A GENERAL ORACLE DATABASE PRESENTATION:

It was acutally prepared for developers.

• TOAD FOR ORACLE  , DB ADMIN AND SQL OPTIMIZER PRESENTATION:

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.