Re: Redo for the Undo

Hi Jonathan,

that makes sense, Thank you.
What we see are "log file switch (checkpoint incomplete) ". I first wanted to understand the basics before jumping in a conclusion. The "log file switch (checkpoint incomplete)" are always accompanied by big deletes (> 100 million rows).
The deletes are a necessary but not sufficient criteria. Since we see big deletes without "log file switch (checkpoint incomplete)" waits. "log file switch (checkpoint incomplete) " means that DBWR can not keep up with LGWR. I wondered how that happens. My guess was that it is connected with undo, since this is a lot more data than redo. Indeed there is a 95% correlation between "Undo blocks written by DBWR" and the size of the delete. I understanding is further, that DBWR has to write blocks, whilst LGWR writes change vectors. In addition it might happen that the DBWR has to write the same block multiple times, if rows are deleted from that block, but not simultaneously.
The delete statement might be some indication. The where condition contains the transaction date in a range comparison (delete /*+ Index_ffs(t) */  table1 t where partition_key = :b1 and transaction date < :b2).
The only useable index looks like this (partition_key, col1, col2, transaction_date). Col1 is highly selective, col2 has 3 distinct values. The software vendors does not want to create an other index on that table because of one (big) delete per day. I can go along the that, but

• Index_ffs does not guarantee any sort sequence, as a consequence the same datablock might by multiple times during the delete whilst deleting rows within that block
• FTS is slower than the Index scan, but rows of the same blocks are deleted in short sequence, which might result in less work of the DBWR
• How about sorting index rowids as an intermediate step?
• Index Range scan might allow batching up deletes (not sure of that)

Can you (or anybody) comment on the points above?

This is an OLTP environment and deletes vary a lot in size. I think the usual strategy as of Insert and exchange partition will not work.

Thanks

Lothar

Am 16.12.2023 um 00:05 schrieb Jonathan Lewis:
> Possibly the difficulty in figuring it out comes from the following:
>   "since roll forward will generate the related undo anew"
>
> Rolling forward simply reads and applies a set of redo change vectors,
> ensuring that either all or none of the redo change vectors in a
> single redo change record are applied. (Oracle may skip a load of redo
> change records at the start of the first (archived) redo log file
> being used, but once it has started applying records a failed records
> will cause the roll forward to terminate.)
>
> Applying a redo change vector to a table block does NOT result in any
> undo being generated. The undo for that table block change is written
> into the correct block in the undo tablespace because the redo record
> holding the redo change vector for the table will also hold a redo
> change vector for the undo record originally generated by the change
> to the table.
>
> Regards
> Jonathan Lewis
>
>
>
> On Fri, 15 Dec 2023 at 08:04, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>
> Hi Mladen,
>
> thanks. Got that. Having a hard time to figure why this would be
> needed.
> I guess after a database crash when you restore a backup the before
> images prior to the restore point might be missing. Therefore roll
> forward will reinstall them in order to be able to cope this flash
> back.
> Other than that I see no reason for it since roll forward will
> generate
> the related undo anew.
>
> Or, probably more generally speaking, restore should reconstruct the
> database state in its whole, not leaving out parts (like undo).
>
> Thanks
>
> Lothar
>
>
>
> Am 13.12.2023 um 15:34 schrieb Mladen Gogala:
> > On 12/13/23 07:53, Lothar Flatz wrote:
> >> Hi,
> >>
> >> can a conventional delete produce redo for undo? And why? What I
> >> don't get: if the redo is applied, undo would be generated anyway,
> >> would it not?
> >>
> >> But maybe my idea of roll forward is a bit simplistic. Not
> really my
> >> area of expertise.
> >>
> >> Thanks
> >>
> >> Lothar
> >>
> >> --
> >> http://www.freelists.org/webpage/oracle-l
> >>
> >>
> > Hi Lothar,
> >
> > Delete changes blocks, UNDO blocks among other things. REDO
> mechanism
> > protects the UNDO tablespace as well and generally writes any block
> > change to the UNDO tablespace.
> >
> > Regards
> >
> > --
> > Mladen Gogala
> > Database Consultant
> > Tel: (347) 321-1217
> > https://dbwhisperer.wordpress.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l