This content has been marked as final. Show 10 replies
974670 wrote:That would have been my first idea for the scenario you describe. How did you change the table and indexes to use a higher initrans value?
... We increased INITRANS and rebuilt the tables and indexes on them (with higher value than DOP used in DML).
For example an alter table statement is not enough. Also not a truncate+reinsert.
The initrans parameter is already used for the initial extent. This is not freed/reused easily.
And this can be changed only if you do a
operation (or drop and recreate).
alter table move tablespace
1) Is the statement type listed above a valid statement which should run correctly?Do you see already the wait events and what kind of waits do you experience? An ITL shortage would be something like an TX 4 wait if I remember correctly.
2) If it is a valid statement are there any other situations which can cause deadlocks (except ITL shortage, missing indexes on FKs, conflicting transactions)?
3) Are there any oracle bugs related to deadlocks which may apply here? (I did some search online, however haven't found anything interesting)
4) Can we turn on more tracing to capture information which could help solve the riddle? If yes what would that tracing be?
In the oracle sql developer the session view will also have a detail tab about wait events and another one about conflicts (blocking locks etc.).
Edited by: Sven W. on Dec 3, 2012 11:59 PM
Thank you for response.
I did "alter table move", and indexes were simple dropped and recreated with higher INITRANS. I don't see any ITL waits for that statement/segment neither for indexes. I do not want to copy here trace files as they probably will not be enough to debug anyways.
The interesting thing is we can' replicate issue anywhere, so this happens only in one environment, one statement when nothing else is running and it doesn't happen every time. Sometimes when it happens and transaction is rolled back, the next re-run is fine.
I just wanted to make sure:
1) The statement type we use is fine(parallel update with exists with correlated subquery)?
2) Can we do something to get more trace info?
3) Is there any known bug which could apply here?
The version of oracle we use is 11 g r2.
Thank you very much for any ideas.
One more detail: the statement is executed within PL/SQL block where other update for the same table is executed as well with commit after each statement (I know default commit behaviour changed for PL/SQL blocks, but it probably doesn't matter here, as I tried COMMIT WRITE WAIT without any results anyways)
- in the original query there seems to be join missing. optimizer still has to join two tables prior to get to update, right ? make it explicit.
update t1 ... where exists (select ... from t2 <no t1 here ?> where t1.id = t2.id)
- other situations to get ora-60 by Tom Kyte ( http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1528515465282 )
"... You are updating more then one row in <tab> but must be getting them in a different order so one session
updated 1 row, another row 5 (for example) then the first session tried to update row 5
(blocks) and the second session tried to update row 1 (deadlock)..."
- partition 65+ mln table and update each partition with one (parallel 1) session - must be no locks ;-)
I don't think anything is missing in the statement:
UPDATE /* parallel(16) */ t1
SET col1 = 'some value'
WHERE t1.id = t2.id
It is a simple update in table t1 whenever we have corresponding rows in table t2 and there as join criteria specified as well: "WHERE t1.id = t2.id"
So it is an update with exists clause containing correlated subquery. So please elaborate more on what do you mean? Are you saying that statement is incorrect? Or oracle doesn't treat this type of updates as valid?
Tom doesn't describe single statement executed as parallel DML, but a case of multiple statements executed by multiple parallel sessions, so his case is much different.
In our case because how oracle splits work for a single parallel DML statement, there should be no collisions on rowid level in table t1 and as I understand they can happen on block level, however it should not be the case as we increased INITRANS for table t1 and its indexes.
As far as partitioning to partition table only to be able to run process serially doesn't quite make sense if we could use simple parallel dml even on non partitioned table.