RE: ITL deadlocks question
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "McPeak, Matt" <vxsmimmcp@xxxxxxxxxx>, Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 24 Jan 2014 22:21:28 +0000
You might look at the dbms_redefinition package.
I won't be surprised if it excluded clustered tables - but it's worth checking.
(If it does allow them it will generate a lot of redo)
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: McPeak, Matt [vxsmimmcp@xxxxxxxxxx]
Sent: 24 January 2014 20:43
To: Jonathan Lewis; Oracle Mailinglist
Subject: RE: ITL deadlocks question
That’s what I was afraid of… thank you for the quick and definitive answer!
One follow-up, if I may…
>> You need to recreate the table with initrans set to at least 8 to be safe.
Since we’re talking about a single-table hash cluster here, am I correct in
believing that there is no online way to do this in 11gR2?
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx]
Sent: Friday, January 24, 2014 3:01 PM
To: McPeak, Matt; Oracle Mailinglist
Subject: RE: ITL deadlocks question
No.
Unless it's changed recently, a session will try each ITL slot in turn for a
few seconds and then stop on one of them once it's gone through the entire
list. If you're lucky the time it takes to cover the list means it will find a
slot even if there were none when it started looking. You need to recreate the
table with initrans set to at least 8 to be safe.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx] on behalf of McPeak, Matt [vxsmimmcp@xxxxxxxxxx]
Sent: 24 January 2014 19:24
To: Oracle Mailinglist
Subject: ITL deadlocks question
I have a situation where the users like to submit eight (8) copies of an I/O
intensive job simultaneously – one job for each of our product lines.
The job operates on tables that are not partitioned (or otherwise physically
separated) by product line, so that one database block may contain rows for
many different product lines.
Occasionally, some of the processes are failing due to ITL deadlocks.
My question is: suppose you have:
Block 1 => ITL: txn A, txn B with txn C waiting.
Block 2 => ITL: txn B, txn C with txn A waiting…
Block 3 => ITL: txn C, txn *R* with txn B waiting…
Is Oracle’s ITL deadlock detection smart enough to realize that, in block #1
for example, txn C is waiting for *either* txn A *or* txn B to end, but that it
need not wait for both?
In other words, is it smart enough to know that the situation above is *not* a
deadlock? (Because txn R can still end, then txn B, then both txn C and txn A
can continue.)
The two tables involved are each in their own single table hash cluster, if
that matters.
Thanks in advance for any help!
Matt
Other related posts:
