Re: Long transaction & LOG_CHECKPOINT_INTERVAL

Hi, Richard,
Thank you so much!
I think it should be something which I understand wrong about Oracle server. Now I get it.
Regards,

Richard Foote wrote:

> 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 - 07:42:54 CDT