Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Deadlocks after upgrade

RE: Deadlocks after upgrade

From: Sonja Šehović <sonja.sehovic_at_pbz.hr>
Date: Thu, 12 May 2005 15:09:18 +0200
Message-ID: <B3772712305A314CAB52855AF8F273960CDA21C9@ksdcexc01.sit.pbz.hr>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US