Re: Data Buffer Cache

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.

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.

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 - 11:02:35 CDT