Re: ITL related question

Oracle uses an optimistic locking strategy for a DML statement (until it fails, then it rolls back the statement and starts again with a pessimistic strategy)

This means that as each relevant row is identified an ITL entry is acquired in its block for the transaction (if an earlier row hasn't already done so). ITL entries are "occupied" until the transaction commits.

Regards
Jonathan Lewis

On Fri, 6 Aug 2021 at 14:58, Hameed, Amir <amir.hameed_at_sleepnumber.com> wrote:

> Hi,
>
> When Oracle runs a DML statement that contains a subquery, does Oracle
> grab an ITL entry immediately at the start of the statement or does it wait
> until row-set from the subquery/subqueries are returned? The reason I am
> asking is that if it is the former then depending on the efficiency of the
> subquery, it could occupy the ITL for a while?
>
>
>
> Thanks,
> Amir
>
>
>

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