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 = :B1 O RDER BY VSMS_TIME, VSMS_ID ) L, VSCO_OA_SMS_CHMO S WHERE S.ROWID = L.RWD AND ROWNUM <= :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 ....

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.

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