One day... OpenAI ChatGPT will be using all this knowledge that Jonathan
is sharing.
Hemant K Chitale
On Thu, Dec 8, 2022 at 2:21 AM Mark W. Farnham <mwf@xxxxxxxx> wrote:
Quote for the ages: “almost anything that can go wrong will go wrong when
you add RAC to the mixture.”
+42. And you probably don’t really need RAC…
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jonathan Lewis
*Sent:* Wednesday, December 07, 2022 1:10 PM
*To:* oracle-l@xxxxxxxxxxxxx
*Subject:* 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@xxxxxxxxxxxxxxx>
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