Re: Question on underlying mechanics of UNDO processing

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_at_rsiz.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jonathan Lewis
> *Sent:* Wednesday, December 07, 2022 1:10 PM
> *To:* oracle-l_at_freelists.org
> *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_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