Re: log manager in ORACLE?

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:26:55 CST