Re: Data Buffer Cache

"Michael J. Moore" <hicamel_x_the_spam_at_attbi.com> wrote in message news:ynRg9.482564$UU1.77684_at_sccrnsc03...
> >
> > You've missed one. LGWR flushes to disk:
> >
> > 1. At every commit
> > 2. When the log buffer is a third full
> > 3. When 1Mb of unflushed redo is outstanding
> > 4. BEFORE DBWR
> >
> > Number 4 is the one which means that DBWR certainly does post LGWR.
> >
> > Regards
> > HJR
> >
> >
> Hi Howard,
> I wonder if your assumption about the meaning of number 4 is correct.
> I am looking at the OCP Fundamentals 1 Exam guide and on page 19 it gives
> the 4 conditions as you stated, but on page 18 it says that DBWn does
 writes
> for any of the following reasons ... and then reason number 2 is
> "When DBW0 is told to write data to disk by LGWR"
>

This is getting a tad tedious, since there's really no mystery here. DBWR writes at a checkpoint. Agreed? What causes checkpoints? Er, well... clean shutdowns, begin backups, offline tablespace.... AND log switches. So when LGWR moves from one redo log to another, sure it posts DBWR.

Does that mean DBWR doesn't post LGWR? Nope.

> So, it appears HERE that LGWR it telling DBWR. I suppose that it is
 possible
> that each could tell the other, but I doubt if that is the way it works.
>

You doubt wrong.

> The to further complicate the matter, at the top of page 19 it says, "The
> event that
> causes LGWR to tell DBW0 to write to disk is called a checkpoint."
>

Correct.

> Great!, now we have 2 votes for LGWR tells DBW0 and 1 vote for DBW0 tells
> LGWR.
>
> But the idiocy is not over! Later in the same book on page 129 it says"
> "The events that occur at log switch are as follows. First, LGWR stops
> writing
> the redo log it filled. Second, CKPT signals the DBWn to flush the dirty
> buffers into
> the data files and ...."
>

That's just plain wrong. The CKPT process is the last thing that fires at a checkpoint event. It doesn't post DBWR, but DBWR posts it.

> So, how can we reconcile all of this?

By ignoring incorrect information, for a start.

>At the risk of being bitch slapped by
> Ricky
> again I will wager another GUESS. How about
> 1)LGWR finishes filling a log file and then throws a checkpoint and then
> waits

It doesn't wait for anything. That would be a recipe for absolutely abysmal performance. When LGWR switches log, it posts DBWR to flush the buffers dirtied by transactions contained in the log just switched away from. It then merrily gets on flushing to the next log. The only time a log switch would cause LGWR to pause or wait would be if the switch was *into* a log for which DBWR hadn't finished its flushing activity yet (a 'checkpoint incomplete').

> 2)CKPT signals the DBWn to flush the dirty buffers into the data files

CKPT does nothing of the sort.

> 3)CKPT updates the control files and datafile headers

AFTER DBWR posts *it* that it's flushing activity has been completed. There would be zero point in updating the headers if DBWR was about to write more information to those same datafiles, would there?

> 4)CKPT finishes his job and DBWn finishes his job

Wrong, wrong, wrong. LGWR switches. Posts DBWR to flush. LGWR continues to flush to the new log. DBWR is flushing madly the older dirtied blocks. When DBWR has finished flushing, it posts CKPT to update the headers.

> 5)LGWR completes the log switch

Nope. The log switch happens regardless of what anything else on the database is doing (unless its a switch into a log for which the previous checkpoint hasn't completed yet).

> IF this is true, then LGWR does (in a way) trigger DBW0 albiet INDIRECTLY
> via the checkpoint mechanism.

There's nothing indirect about it. LGWR posts DBWR at every log switch. That's what a checkpoint is...DBWR flushing dirty blocks to disk.

And DBWR has a completely independent schedule, whereby it can flush for its own reasons (such as an abundance of dirty buffers). And if it flushes for its own reasons, it posts LGWR to flush the redo log buffer first.

> But Ricky is also correct to say that LGWR DOES NOT post DBW0, as the
> invocation is really the side effect of of issuing a checkpoint.
>

Not true at all.

Really, this is DBA-101. There is no mystery about it -or at least, there shouldn't be. I teach this on Day 1 of the introductory DBA course. It's basic stuff.

> So, even though Oracle's own documentaion says,
> "The redo log buffer writes to the redo log file under the following
> situations:"
> 1) omitted
> 2) omitted
> 3) omitted
> 4) "Before DBW0 writes modified blocks in the database buffer cache to the
> datafiles"
>
> I suspect that #4 is highly misleading and hence Ricky omitted it.
>

It isn't misleading at all, and if it didn't happen, we'd be into unrecoverable instance crashes big time. It is compulsory for LGWR to flush before DBWR decides to do so. Therefore, DBWR *MUST* post LGWR.

Regards
HJR
> Ricky?
>
> regards,
> Mike
>
>
>
>
>
>
>
Received on Sat Sep 14 2002 - 21:00:51 CDT