Re: log manager in ORACLE?

Hi, Howard,
Thank you so much! ! !
Good night!
Y

"Howard J. Rogers" wrote:

> No problems.
>
> Data in buffers is flushed to disk by DBWR, and he fires off:
>
> 1. When there are too many dirty buffers (the short version is that you can
> set a parameter called DB_BLOCK_MAX_DIRTY_TARGET which specifies the maximum
> number of dirty (ie, changed) buffers to hold in memory, When you dirty
> more than that number, DBWR flushes some or all of them to disk -the exact
> formula for how many it decides to flush is rather complicated).
>
> 2. When there are too few free buffers. When your transaction needs to
> place a fresh block of data in the buffer, it has to walk along the Least
> Recently Used list, trying to find a spare one it can use. If that walk
> takes too long, your transaction is forced to wait -clearly not a good idea.
> So when your transaction has walked too far along the LRU list without
> finding a free buffer, it signals DBWR to flush dirty buffers to disk
> (thereby freeing them up for being over-written). The threshold used to
> determine when is 'too long' is not settable (OK, it is -there's a hidden
> init.ora parameter which can do it, but you'd be ill-advised to play with
> hidden parameters. They're hidden for a reason, normally!)
>
> 3. Every 3 seconds, DBWR wakes up to see if there's anything worth
> flushing. DBWR dislikes dribbling one block here and there to disk... it
> likes to write a nice, large batch of dirty buffers. So when it wakes up,
> it might not actually flush -there just aren't enough dirty buffers to make
> it worth its while. But then again, some other occasion when it wakes up,
> it might be worth its while, and a flush will take place.
>
> 4. At a checkpoint. A checkpoint can be triggered by all sorts of things:
> a clean shutdown, for example. Putting a tablespace into hot backup mode.
> Making a tablespace read only. Taking a datafile offline. A log switch
> (ie, a move by LGWR from writing to one redo log group to another). All
> these things trigger DBWR to flush buffers that would be affected by the
> triggering event (in other words, taking a tablespace offline causes only
> buffers related to that tablespace to be flushed, but not others; a log
> switch causes buffers covered by transactions in the log being switched away
> from to be flushed, but not others. A database shutdown causes (naturally
> enough) every single buffer to be flushed.
>
> The key point is that DBWR has its own schedule of events, and none of these
> events bears any relation to the state of a transaction. You can be in the
> middle of a transaction, and still have your buffer (and its associated
> rollback buffers, of course) flushed to disk if we need the space. Or (and
> here's the real performance booster) you can issue a commit and NOT have
> your buffer flushed to disk.
>
> Regards
> HJR
>
> "Y" <newdbms_at_yahoo.com> wrote in message news:3C69DA1E.9F01579C_at_yahoo.com...
> > Greate! Thank you so much Howard!
> > Would you tell me when the data in buffers will flush to datafiles in
> Oracle?
> > Best Regards,
> > Y.
> >
> > "Howard J. Rogers" wrote:
> >
> > > Comments below.
> > >
> > > "Y" <newdbms_at_yahoo.com> wrote in message
> news:3C6881C6.45770644_at_yahoo.com...
> > > > Hi,
> > > > I am using Oracle 8i EE.
> > > > I took a quick view of oracle books. I haven't found a direct answer
> for
> > > > my questions.
> > > > I just want to know how log manager works in Oracle?
> > > >
> > > > For example, when I issue one update sql request,
> > > >
> > > > 1. Oracle read the datablock from datafiles on disk into buffer.
> Before
> > > > update data block, oracle first write the before and after image to
> log
> > > > buffer?
> > > >
> > >
> > > Pretty accurate so far. Actually, it takes a lock on the row(s) first,
> then
> > > writes the before and after images into the log buffer.
> > >
> > > > 2. When and what information will be written to Rollback Segments?
> > > >
> > >
> > > Immediately after that, it writes just the before image of the row or
> > > affected columns into the redo block (which it reserved for the purpose
> > > somewhere around question 1).
> > >
> > > > 3. When the transaction end or the log buffer pool full, log buffer
> > > > pool flush to redo log files?
> > > >
> > >
> > > Nope. The log buffer is flushed whenever it is one third full, so it
> should
> > > never get totally full. Ending the transaction with a commit will
> indeed
> > > cause a flush, however. LGWR will also flush if 1Mb of uncommitted redo
> has
> > > been generated, and the buffer is bigger than 3Mb (otherwise the 1/3rd
> full
> > > rule would always kick in earlier). And LGWR will always flush anytime
> DBWR
> > > signals it wants to flush (DBWR is working to its own agenda, and can
> flush
> > > for all sorts of reasons, none of which are transaction specific. But
> > > whenever it threatens to flush, it's crucial that LGWR finishes its
> flush
> > > first, since it is the entries in the redo logs that lets us sort out
> the
> > > mixture of committed and uncommitted data we'd find in our datafiles
> after
> > > an unexpected shutdown.)
> > >
> > > > 4. The rollback segments will only keep before and after image for
> open
> > > > transactions. Is that correct? The redo log files will store the
> before
> > > > and after image of unactive transactions. Is that correct?
> > > >
> > >
> > > No. Rollback segments store the before image of transactions whether
> they
> > > are committed or not. We need the before image of committed
> transactions to
> > > generate read consistent images of the data for queries which *started*
> > > before transactions were committed. However, it is permissible to
> overwrite
> > > the rollback of transactions which have been committed -but if we do,
> you'd
> > > best pray that no reports need that rollback for read-consistent image
> > > purposes, otherwise you'll have an ORA-1555 Snapshot Too Old error
> message
> > > generated.
> > >
> > > It's also the case that when LGWR flushes the log buffer, it flushes ALL
> > > previously unflushed transactions into the redo logs. That can include
> > > committed as well as uncommitted transactions. Therefore, the redo logs
> > > store the before and after image of ALL transactions, committed or
> > > uncommitted makes no difference (or, in your terms, 'active' or
> 'inactive').
> > >
> > > Regards
> > > HJR
> > >
> > > > Thanks for any help?
> > > > Y
> > > >
> >
Received on Tue Feb 12 2002 - 21:49:20 CST