Howard,
Thank you very much for devoting some of your time explaining what you have
read/experienced.
Very much appreciated ! (just in case - no I'm not sarcastic )
Regards,
Keld
"Y" <newdbms_at_yahoo.com> wrote in message news:3C69E26D.73D0CEA4_at_yahoo.com...
> 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 Wed Feb 13 2002 - 15:31:40 CST
