RE: [EXTERNAL] Re: ITL related question
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-lReceived on Fri Aug 06 2021 - 16:24:38 CEST