Re: Question on underlying mechanics of UNDO processing

(4) and (5) nothing particularly different to worry about, but you MIGHT find that the inserting process finds that it wants to insert into a block that the DELETE process is currently rolling back and apply the undo for it, so when the delete gets to the point where it should be applying undo to that block it doesn't need to because the relevant undo records have already been marked as "use applied undo". (This is what can happen on instance startup - Oracle doesn't wait for smon to complete the rollback before opening the database, it allows any process to rollback a block if it needs to, and smon then skips over the undo records that have been applied.)

5) if there's a row that has been deleted and the delete needs to be rolled back nothing "new" happens. The insert could only have happened if the space available BEFORE the delete was large enough, so there are no extra complications - you're just likely to see another case of waiting for a gc gurrent get.

Regards
Jonathan Lewis

On Thu, 1 Dec 2022 at 19:17, Hameed, Amir <amir.hameed_at_sleepnumber.com> wrote:

> Hi,
>
> I am trying to understand the internals of UNDO in the following scenario.
> The database is a three-node RAC version 11.2.0.4:
>
>
>
> 1. A DELETE statement is running on node#3. The statement is deleting
> around 99 million records from table T1. The program running this statement
> has no commit baked into the process.
>
> DELETE FROM T1 WHERE TRUNC(TIMESTAMP) < :1 – 1
>
>
>
> 1. An INSERT statement runs on node #1 inserting records into table T1:
>
> INSERT INTO T1 ( LOG_SEQUENCE, …..)
>
>
>
> 1. At his stage, if the INSERT statement (node#1) is trying to insert
> a record into the same data block of T1 where another record is being
> deleted by the DELETE (node #3) statement, how will Oracle process it? What
> kind of waits/contention the session running INSERT might run into?
>
>
>
> 1. The process running the DELETE statement (node #3) is killed
> because it was taking too long to finish, the UNDO segment has millions of
> UNDO blocks that the killed process is now rolling back.
>
>
>
> 1. At his stage, if an INSERT statement (node#1) tries to insert a
> record into a data block of T1 that had a deleted row that is being rolled
> back now, how will Oracle process it? What kind of waits/contention the
> session running INSERT might run into?
>
>
>
> A feedback would be greatly appreciated.
>
>
>
> Thank you,
>
> Amir
>

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