Re: Data Buffer Cache

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Sun, 15 Sep 2002 07:16:09 +1000
Message-ID: <3d83a71d@dnews.tpgi.com.au>

"Ricky Sanchez" <rsanchez_at_more.net> wrote in message news:3D835E0E.CC5AA053_at_more.net...
> Richard-
>
> Settle down, dude! My remark was specifically toward Michael Moore's:
>
> "These are just my guesses. I hope somebody who KNOWS will respond.
> Mike".
>
> In my reference to "...the misleading replies already posted", I ought
> to have said "remarks" rather than "replies", to be better directed. I
> suppose I ought to have specifically excluded your post from that
> sentence, since your remarks were mostly correct.
>
> But since you bring it up, your remarks were not entirely accurate. Not
> so egregious -- more deserving of a nitpick than a full-force bitch
> slap.
>
> You said, "If the DBWR is "woken" up, it wakes up the LGWR to flush it's
> current contents down to disk." I can think of no circumstances under
> which dbwr posts lgwr, ever.
>

Bong! DBWR flushes data blocks to disk under the following circumstances:

At a checkpoint (caused by shutdown, begin backup, alter tablespace...offline etc)
When there are insufficient clean blocks (your server process spends too long walking down the LRU list looking for a free buffer into which to place your requested block of data, and posts DBWR as a result)
When there are too many dirty buffers (such as may be indicated by reaching db_block_max_dirty_target in releases prior to 9i)
Every three seconds it wakes up to see whether the dirty list (aka 'the checkpoint queue') is big enough to warrant flushing, regardless of whether it has hit the max_dirty_target.

In every one of those cases, DBWR might be about to flush a dirty buffer to disk. And in every one of those cases, DBWR will post LGWR to flush the redo which caused those to be dirtied *before* it is permitted to itself flush the dirty buffers.

Were DBWR to fail to post LGWR in this fashion, and were we then to have an immediate Instance failure, we would then have an unrecoverable block on our hands: something modified the block's contents, but we don't know what it is, because the redo involved was lost.

> Lgwr does not necessarily flush the log buffer when dbwr is woken up.
> Lgwr normally writes when the log buffer is 1) 1/3 full or 2) has at
> least 1 meg of stuff to write. It does respond to a commit, however,
> which results in a "log file sync" to ensure redo is written ahead of
> table data.
>

You've missed one. LGWR flushes to disk:

At every commit
When the log buffer is a third full
When 1Mb of unflushed redo is outstanding
BEFORE DBWR

Number 4 is the one which means that DBWR certainly does post LGWR.

Regards
HJR
> Transaction commits, of course, come straight out of session processes,
> not background processes. And, there are the inevitable group commits
> that are basically a piggyback of log writes that make lwgr IO calls
> more efficient and preserve redo block order. There is some coordination
> that happens between dbwr and lgwr surrounding the checkpoint queue
> mechanism, but dbwr does not post lgwr. Overall not pertinent to the
> original question, so I overlooked it at the time. Thank you for
> insisting on the correction.
>
> So, no slap down for you, but in retrospect I do think you owe us ten
> "good" pushups. ;-)
>
> As for Sybrand, who knows what drives that boy to such hyperbole?
> Contrary to his paranoia, my posts are never designed to make others
> look stupid, they typically have already done that to themselves. And
> sometimes they do it with such arrogance!
>
> I occasionally offer remarks to clarify technical issues. Often it is in
> the form of a slight nitpick, but most incorrect information in this
> newsgroup is of a minor nature and I ignore it altogether. Every once in
> a while I see something that is either blatanty stupid or dangerously
> misleading and I feel compelled to respond accordingly.
>
> I am not here to promote a web site or a consulting service, although I
> have no quarrel with those who do so, and who actually offer quality
> advice. Nor am I trying to be the world's most famous DBA. In fact, I am
> neither a consultant nor a DBA.
>
> - ricky
>
> Richard Foote wrote:
> >
> > Hi Ricky,
> >
> > I posted one of those "misleading" replies that should be ignored. I
might
> > also be the one who has "put their ignorance into writing", not sure.
> >
> > I'm quite used to people giving me a bit of a blown torch and you need
to
> > join quite a lengthy queue of people who question my sanity.
> >
> > Now, I limited my reply to the questioned asked by the original poster,
> > however, I fail to see anything in my post that is either misleading or
> > which contradicts anything you've stated.
> >
> > So Ricky my friend, I would appreciate being informed on what exactly
was so
> > wrong with my reply ?
> >
> > Cheers
> >
> > Richard
> >
> > "Ricky Sanchez" <rsanchez_at_more.net> wrote in message
> > news:3D829CC4.1C65382B_at_more.net...
> > > Pinaki-
> > >
> > > Ignore the misleading replies already posted. For the life of me, I
> > > cannot understand why someone who admits to not knowing an answer
would
> > > bother to put their ignorance into writing. Must be a net phenomenon.
> > >
> > > That said...
> > >
> > > 1. Rollback segments do not contain the previous image of a changed
> > > block. They contain "redo vectors", which are logical representations
of
> > > the physical changes to a block. It's an op-code sort of thing that is
> > > much more compact than a pre-image of the block. See Jim Gray's
> > > "TRANSACTION PROCESSING: CONCEPTS AND TECHNIQUES" discussion of
> > > physio-logical logging if you are morbidly curious about such stuff.
> > > Just for completeness, I'll state there are circumstances where we
will
> > > actually log the image of a block, but those circumstances are
special.
> > >
> > > 2. Pmon does not get involved at all in redo / undo, except to clean
up
> > > processes that die ungraciously.
> > >
> > > 3. Dbwr never writes redo. Lgwr does that.
> > >
> > > 4. Redo is generated before undo is generated. Undo is generated and
> > > applied before dml changes to a table are applied. Furthermore, there
is
> > > redo for the undo, etc, since rollback segments are just more data
> > > blocks, as far as the buffer cache and the recovery process are
> > > concerned. Much detail omitted here.
> > >
> > > 5. Redo is written to disk before the buffer block is physically
written
> > > to disk. It's a well enforced rule within the Oracle database.
> > >
> > > 6. When you issue a rollback, all that logical change from 4 (above)
is
> > > looked up from the transaction table (rollback segment header), taken
> > > from the transaction ID held in your session's private memory. Much
> > > detail is left out here, but *all* changes made as a product of the
dml
> > > have been previously stored in both rollback segments and the redo
> > > stream. This includes redo of the undo of the changes to the rollback
> > > segment blocks and headers themselves. Lots of work involved, too much
> > > detail to note here.
> > >
> > > 7. During rollback, the undo vectors are applied to each appropriate
> > > block. If a block has since been written to disk, it is retrieved and
> > > "pinned" in the buffer cache. Often, however, the blocks will still be
> > > in the buffer cache. No matter, it is transparent to the transaction
> > > logic. It just happens.
> > >
> > > Let's suppose all this happens, and the instance crashes before the
> > > table data block was written to disk, and *before* either a rollback
or
> > > commit happens. At the start of recovery, the version of the block on
> > > disk is exactly as it was before the start of the update statement.
> > >
> > > Because of 5 (above), a block change (redo operation code) is
> > > encountered in the redo stream. That block is read from disk and
placed
> > > into the buffer cache. Changes are applied to it just like the sql
> > > update statement and you have the "new" block, still uncommitted. Note
> > > that changes to rollback segments and the rollback segment header
> > > (transaction table) are also in the redo stream, so those blocks are
> > > also recovered exactly like table blocks. Moreover, those undo changes
> > > are in the redo stream *before* the table changes, so the rollback
> > > segment blocks are recovered before the table data. Subtle, but
> > > critical.
> > >
> > > If a "commit" redo vector is encountered, the transaction is completed
> > > by placing an indicator in the transaction's "slot" in the transaction
> > > table. That change is now recovered and "safe". If, instead, a
> > > "rollback" is found in the redo stream. All those undo and data block
> > > changes are backed out one a time, based on references found in the
> > > transaction table slot for that transaction. The data block is
restored
> > > to it's previous logical state. Other than different transactional
> > > changes to the same block from other sessions, that rolled-back data
> > > block is now "safe" as well.
> > >
> > > If the redo stream ends without either a commit or a rollback, the
> > > transaction was "in flight" and will be rolled back, most likely by
the
> > > Smon process. The transaction is marked as "dead" at the end of
recovery
> > > and Smon does its thing after the database is opened.
> > >
> > > This is either far more, or far less than you wanted to know.
Certainly
> > > little detail is offered, but perhaps you get the idea.
> > >
> > > Hope this helps.
> > >
> > > - ricky
> > >
> > > Pinaki wrote:
> > > >
> > > > Hello ,
> > > >
> > > > I am working in Oracle 8.1.7
> > > > I am issuing a update statement..."Update tab set x=y where z='abc'
> > > > from a session.I do not commit or rollback and no further statements
are
fired
> > > > from session for 10-15 mins...
> > > > From what I understand the previous image is stored in the Rollback
segment and
> > > > redo information is stored in the Redo log buffer.
> > > > The DBWn meanwhile starts writing the dirty buffers to
the
disk(in
> > > > case of any event such as a log switch etc.).At this point of time
some
of my
> > > > data is in the buffer cache and some in the disk.Now I issue a
rollback
from my
> > > > session.How does oracle handle this situation and take me back to a
position as
> > > > if nothing has happened at all...
> > > >
> > > > Thanks,
> > > > Pinaki
Received on Sat Sep 14 2002 - 16:16:09 CDT