| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Deadlocks after upgrade
Hi!
On Oracle 9.2.x do not use analyze tables, it has some very nasty bugs.
I would suggest that you use:
exec DBMS_STATS.GATHER_SCHEMA_STATS('schema_name',NULL,FALSE,'FOR ALL = INDEXED COLUMNS SIZE 1',4,'DEFAULT',TRUE,NULL,NULL,'GATHER'); or
exec DBMS_STATS.GATHER_TABLE_STATS ('schema_name','table_name',NULL, =
100, TRUE,'FOR ALL INDEXED COLUMNS SIZE =
AUTO',4,'DEFAULT',TRUE,NULL,NULL,'GATHER');
HTH,
Sonja
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of cichomitiko gmail
Sent: Thursday, May 12, 2005 12:48 PM
To: oracle-l_at_freelists.org
Subject: Deadlocks after upgrade
Hi all,
after upgrading an Oracle instance from 8.1.7.4 to 9.2.0.5 it began to =
log a lot of deadlocks. It's was a critical production system so we have =
to find a rapid solution, there was no time to analyze the problem =
appropriately(... I know ... )
The current statement in the trace files was:
SELECT S.ROWID RWD FROM (SELECT ROWID RWD FROM VSCO_OA_SMS_CHMO WHERE = VSMS_TIME BETWEEN (:B2 - 1) AND :B2 AND VSMS_STATUS =3D :B1 O RDER BY VSMS_TIME, VSMS_ID ) L, VSCO_OA_SMS_CHMO S WHERE S.ROWID =3D = L.RWD AND ROWNUM <=3D :B3 FOR UPDATE OF VSMS_STATUS
Deadlock graph:
---------Blocker(s)-------- =
---------Waiter(s)---------
Resource Name process session holds waits process session =
holds waits
TX-000d001f-00018a68 20 225 X 22 209 =
X
TX-0010000f-0000440c 22 209 X 20 225 =
X
session 225: DID 0001-0014-0000029E session 209: DID =
0001-0016-0000030F
session 209: DID 0001-0016-0000030F session 225: DID =
0001-0014-0000029E
My first idea was: CBO/access path/performance issue - may be the first =
session is not able to finish the work and deadlocks with the subsequent =
that is trying to do the same work in different order ....I was not =
convinced, but, in that circumstances, all that I needed was an =
immediate solution.
All I did was "analyze table VSCO_OA_SMS_CHMO compute statistics for =
table for all indexes for all columns size 254" (before the upgrade the =
table was analyzed via the "analyze table ... estimate statistics sample =
10 percent" command) and ... that was the end of all those deadlocks =
....=20
The problem is that I didn't check the access path before the analyze = that I did and I don't know how it changed. Because of the bind = variables I had to trace it first(event 10046) and then check the = execution plan with the values.=20
I didn't understand what happened. Before I write this email I tried to = do a little test to verify if the locking mechanism of the "select for = update" statement is changing with the access path - FULL vs. INDEX/BY = ROWID and I saw that it remains the same: TM Row Exclusive, no metter = how you access the table(FULL vs. INDEX/BY ROWID).
Could someone explain this situation?
Kind Regards
Dimitre
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 12 2005 - 09:15:45 CDT
 |
 |