Re: Interaction between databse cache, DBWR and datafiles

Hi Rachel,

Comments embedded

"Rachel Wilson" <wilsonr_at_logica.com> wrote in message news:936259dc.0301300707.7d093fc2_at_posting.google.com...
> I'm trying to explain the mechanisms of the interaction between the
> buffer cache, dbwr and the datafiles to others and as often happens
> i've confused myself so that I no longer understand myself!

Know that feeling well :)

>
> As I understand it the process works like this
> 1. there are two "lists" describing the buffers: the LRU list and the
> dirty list

Kinda, although there's actually one 'LRU' list per buffer cache latch. We won't go there :)

> 2. when a request to access data is made (to read or write) the LRU
> list is searched from the LRU end up
> 3. if the relevant buffer is found it is moved to the MRU end,
> modified and marked dirty

Not quite the sequence but effectively yes

> 4. if it isn't found, the block is read into the middle of the LRU
> list, or the MRU end, or the first free block from the LRU end
> depending on which document you read(?)

This depends on a couple of factors. Pre 8i, *newly loaded* blocks that are accessed via an index, directly via a rowid, or were cached or "small" tables were loaded into the MRU end. This was then modified somewhat, cleverly in my opinion, such that these newly loaded blocks were placed within the LRU list. If they were truly frequently used then it gives them plenty of time to be touched and moved to the MRU end but if they were only one day wonders, didn't impact the cache as severely waiting for them to migrate all the way along their LRU list.

Larger, non cached tables accessed via a full table scan are loaded into the LRU end, ensuring that their impact on the buffer cache is reduced

> 5. while it is searching for a free buffer any dirty buffers
> encountered are moved to the dirty list
> 6. If no free buffers are found by the threshold limit (or the timeout
> occurs), dbwr writes the buffers in the dirty list to disk

or a "batch" of them ....

> 7. the newly found free buffer is written to and then moved to the MRU
> end and marked dirty therefore moving the older dirty buffers down the
> list

I'm not 100% quite sure what this means. If it means when Oracle finds a block it's looking for in cache, it moves it to the MLU end , then yes.

>
> it is step 4 that most troubles me as the documentation seems to
> contradict itself

Hopefully, I've confused you even more ;)

>
> Also The other questions I can't answer by reading the documentation
> are:
> * Is a buffer "block sized" ie, one block per buffer?

Yes.

> * when dbwr writes a dirty buffer to the datafiles does this buffer
> become empty or is the modified version moved back to the MRU end of
> the LRU list (or to the middle maybe, seeing as we are using 8i).

Basically, the block becomes "cleaned" in that once DBWR has strut it's stuff, the O/S has a copy of the block as currently stored in cache thereby making it safe to be overwritten. It's position within the LRU is unaffected but it's no longer marked as dirty however, being close to the LRU, it's days are numbered.

> * If these written dirty buffers are emptied and therefore free, does
> this not contradict the purpose of keeping recently accessed
> buffers/blocks in memory

No. Because it they were placed in the "Dirty List" they must have been somewhere close to the LRU end. Once cleaned, they are the perfect candidates to be overwritten as they can't have been recently accessed. If they were recently accessed, they would have been "safe" at the MRU end with only the dreaded checkpoint causing them to be written to disk.

> * Does "touching" a buffer mean 'searched' or 'modified'/'read'

It depends. If they are "touched" as a result of a select, then they are not modified but placed on the MRU end (unless they have just been loaded and a delayed block cleanout is required, another time ;). If they are touched as a result of a DML operation, then yes the block is modified and is "dirtied" as a result.

> * if another process wants access to a dirty buffer i think it is
> flagged as having "waiters" but i'm not sure what happens to the
> buffer

Not 100% sure what you mean here but I'll take a stab. A buffer is an area of memory and for obvious consistency reasons, can only be modified by one process at a time. While being modified, this buffer is "pinned" and is effectively locked via a latch mechanism. These "locks" are held for a (relatively) small period of time but if another process wants to modify the same block at the same time, it can't and a buffer busy wait is registered. Once the block has been changed the initial process, the second process can go for it and make it's changes. Each process requires access to a transaction slot in the header of the block so it can identify its transaction details and importantly which row within the block it wishes to modify and lock. Providing a transaction can access one of these precious transaction slots (which aren't released until the transaction ends), then many transactions can effectively modify the same block in cache.

>- does it get written to disk and then read back in?

Hopefully you can now see the answer to this is no

> does the
> second modification get made immediately so only one write occurs?

Yes. A block can be changed indefinitely in cache without having to be written to disk until eventually the block slides down to the LRU end or a checkpoint occurs.

>
>
> I think my problems stem for not understanding the difference between
> "clean" and "free" I would suppose free means empty and clean means
> full but matching the datafile block.

OK. Again somewhat simplistically, a buffer can be empty (which is about as clean as it gets) with no blocks having yet been loaded into it. This can obviously occur at database startup or commonly in the KEEP pool where not enough segments have been defined to it for it to ever to fill up.

It can be "clean" meaning the same version of the block as stored in cache already exists on the OS. This can occur if a block has only been loaded and accessed as a result of a select (delayed block cleanout being an exception), or if a block has been written to disk by a DBWR process and has not been subsequently changed (typically as a result of a checkpoint).

Another type of "clean" buffer is a "consistent read" buffer, which is a buffer that is actually a copy of another block but has been reconstructed to a previous state (a layer(s) of change peeled away if you like) in order to satisfy a consistent read requirement.

A buffer can be "dirty" in that it has been modified in cache and can not be simply overwritten (else the modification would be lost) and has to be at some point in time written back to the OS.

And a subset if you like of a dirty block is one which is "pinned" in that it is currently being modified

>Also I can't see the point in
> writing dirty buffers to disk in order to free them because then they
> are recently used but emptied (if that is indeed the case)

Dirty blocks are only written to disk if they have made their way to the LRU end (which of course means that they are among the least recently used blocks) and have been placed on this dirty list or if a checkpoint occurs meaning all dirty blocks from the point of time of the checkpoint must be written to the OS.

Hope this helps to clear a few things up.

Cheers

Richard Received on Fri Jan 31 2003 - 08:07:20 CST