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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of cichomitiko gmail
Sent: Thursday, May 12, 2005 12:48 PM
To: oracle-l@xxxxxxxxxxxxx
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

Other related posts: