Question on underlying mechanics of UNDO processing

From: Hameed, Amir <amir.hameed_at_sleepnumber.com>
Date: Thu, 1 Dec 2022 19:16:47 +0000
Message-ID: <CH0PR14MB501249A4E55D374AAF405271E0149_at_CH0PR14MB5012.namprd14.prod.outlook.com>

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?
2. 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.
3. 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

Received on Thu Dec 01 2022 - 20:16:47 CET