Re: Interaction between databse cache, DBWR and datafiles

Phew! I'll have to go digest this and rearrange my brain :)

Thanks very much guys

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<Wgu_9.37447$jM5.95502_at_newsfeeds.bigpond.com>...
> 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 Feb 21 2003 - 09:32:20 CST