Re: Long transaction & LOG_CHECKPOINT_INTERVAL

Hi again !

Ok, I think I understand your confusion.

The key point to note is that the log_checkpoint_interval only helps to control "instance" recovery times, not "transaction" recovery times. As part of the instance recovery, the redo logs are accessed to reconstruct the rollback (undo) segments to the time of the crash. Once this has occurred, the database is opened (meaning everyone can now merrily log in).

However the "transaction" now needs to be recovered as all the changes it made were never committed and hence needs to be rolled back (using the now fully recovered rollback segment). Note the rollback phase of recovery is performed AFTER the database has been reopened (that's what needs highlighting).

A way to reduce possible transaction recovery times is reduce the size of the transaction (i.e. commit more frequently if possible or appropriate).

Hope this helps

Richard

"Y" <Y_at_y.y> wrote in message news:3D057149.44ECC287_at_y.y...
>
>
> Richard Foote wrote:
>
> > Hi
> >
> > I'm not sure I understand your understanding but I hope this helps.
> >
> > Providing log_checkpoint_interval is the most aggressive of the
checkpoint
> > targets, once exceeded, DBWRs are requested to write down those blocks
that
> > correspond to the oldest redo that exceeds the log_checkpoint_interval.
Once
> > complete, we now only have to recover the log_checkpoint_interval amount
of
> > redo during instance recovery.
> >
> > From this point on, subsequent writes to the redo log will again exceed
this
> > target and so DBWR is continually requested to write these offending
blocks
> > to disk (note if these blocks are still in memory).
> >
> > With your long transaction, once it has generated
log_checkpoint_interval
> > amount of redo, it's earlier changed blocks are being written down by
DBWRs
> > to ensure instance recovery requirements are met.
>
> Thank you, Richard!
> But I still can't understand this issue.
> Yes, I do believe the DBWRs would flush the dirty blocks which both the
long tx
> and other transactions made when whatever conditions trigger
> log_checkpoint_interval. The problem is even so, the long transaction is
still
> active, at this moment, if the instance crash, during the recovery, the
long tx
> have to be rollback. it need to reapply those undo blocks (either they
flush to
> disk during the checkpoint or not) to do the rollbak. How to gurantee
those
> number of undo blocks must less than the log_checkpoint_interval number of
redo
> blocks.
>
> >
> >
> > Depending on the performance of DBWRs, it might not quite be able to
keep up
> > and in actuality more than log_checkpoint_interval of redo may need to
be
> > recovered but Oracle is actively trying to keep to this target and all
> > things being equal should be there or thereabouts (see
v$instance_recovery).
> >
> > Hope this helps
> >
> > Richard
> >
> > "Y" <y_at_y.y> wrote in message news:3CFC29FD.97A707EC_at_y.y...
> > > Why we say LOG_CHECKPOINT_INTERVAL ensures that no more than
> > > log_checkpoint_interval number of redo blocks are read during instance
> > > recovery.
> > >
> > > Checkpoint only ensures those dirty pages in buffers will be written
to
> > > disk. If there is a long transaction which will span more than one log
> > > file. During the instance recovery, there are maybe more than
> > > log_checkpoint_interval will be need to be read.
> > >
> > > Would you tell me where my understanding is wrong?
> > >
> > > TIA
> > >
>
Received on Tue Jun 11 2002 - 00:43:03 CDT