RE: [EXTERNAL] Re: ITL related question

From: Hameed, Amir <amir.hameed_at_sleepnumber.com>
Date: Fri, 6 Aug 2021 14:24:38 +0000
Message-ID: <CH0PR14MB5012C532E9A814BED533DE61E0F39_at_CH0PR14MB5012.namprd14.prod.outlook.com>



Thank you, Jonathan.

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Jonathan Lewis Sent: Friday, August 6, 2021 10:16 AM
To: ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org> Subject: [EXTERNAL] Re: ITL related question

CAUTION: External source

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<mailto: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
Received on Fri Aug 06 2021 - 16:24:38 CEST

Original text of this message