Re: Redo for the Undo

Lothar,

Thanks for the answers.
The thought behind my questions was about the viability of using the option for:

alter table X move partition Y
including rows where {predicate}
online
update indexes
;

Moving a partition to "forget" the rows you want to delete may produce a lot less undo (virtually none), and if the remaining rows are far fewer than the number of "deleted" rows the redo for row creation and index recreation may be much smaller than the original redo.

Given your example of the delete statement using bind variables you may have to use the "partition for" syntax.

Regards
Jonathan Lewis

On Mon, 18 Dec 2023 at 14:43, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> Hi Jonathan,
>
> welcome advise, thanks.
>
> Which version of Oracle? 19.11
> Is there only one partition key value per partition ? yes, list
> partitioned , one value per partition
> Is the index you've described unique or the primary key index? it is the
> PK Index
> (I'm always suspicious of any index on a partitioned table that
> starts with the partition key column(s) - it's often a waste of space)
> agree
> Are there any global indexes and, in particular, is the index you've
> described global or local? All Indexes are local
> How many rows are in the partition you're deleting from, and how many rows
> are left after the deletion is complete? Difficult. I can't see the state
> before the delete any more. It seems to me not all records get deleted.
>
>
> Regards
>
> Lothar
> Am 18.12.2023 um 10:49 schrieb Jonathan Lewis:
>
>
> Lothar,
>
> Just in case you find something of interest in one of my older articles,
> here's a link to a little catalog on "Massive Deletes": Massive Delete |
> Oracle Scratchpad (wordpress.com)
> <https://jonathanlewis.wordpress.com/2018/06/08/massive-delete/>
>
> The volume of undo comes from the need to stored the entire (inline)
> portion of the deleted row in the undo segment, and things that can make
> the volume worse are:
> a) the number of indexes that have to be updated at the same time
> b) extra undo relating to any migrated or chained rows
> c) whether Oracle can create one undo record per BLOCK or whether it has
> to create one record per row
> d) whether the index maintenance can be done as a delayed sorted array
> delete (block level) or in "real-time" row at a time
> e) the version of Oracle
>
> It is possible to "optimize" deletes - but it's not a trivial exercise to
> predict the best strategy without know the data and indexing very well.
>
> A tablescan or index-fast-full-scan delete will be doing row at a time
> deletes with real-time index maintenance; and that could lead to a lot of
> extra random I/O and (for very large objects with lots of indexes) lots of
> index leaf blocks being written and re-read multiple times (and the IFFS
> scan could result in lots of table blocks also being written and re-read).
> (So that index_ffs() hint looks as if it may be a bad idea).
>
> An index range scan can delay all the index maintenance and then do it as
> efficiently as possible, but that a large accumulation and sort of key
> values. If the checkpointing is the big threat, though, this may be a
> strategy that moves the stress away from the log writer. An alternative
> would be to add more redo log file space though.
>
> Which version of Oracle?
> Is there only one partition key value per partition ?
> Is the index you've described unique or the primary key index?
> (I'm always suspicious of any index on a partitioned table that starts
> with the partition key column(s) - it's often a waste of space)
> Are there any global indexes and, in particular, is the index you've
> described global or local?
> How many rows are in the partition you're deleting from, and how many rows
> are left after the deletion is complete?
>
> Regards
> Jonathan Lewis
>
>
> On Mon, 18 Dec 2023 at 07:57, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>
>> 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