Niall, Almost all sessions runs this query frequently followed by an update statement. During a dead lock situation, I found most of the session waiting on 'enq: TX - row lock contention' wait event. I would say table2 is a core table in this application. Regards, Jaffar On Tue, Dec 15, 2009 at 10:57 AM, Niall Litchfield < niall.litchfield@xxxxxxxxx> wrote: > If it's a deadlock what are the other transactions doing? That looks > at first right and implying some constraints from the index names a > reasonable plan for that part of the puzzle? > > On 12/15/09, Syed Jaffar Hussain <sjaffarhussain@xxxxxxxxx> wrote: > > Hi everyone, > > > > The following simple join query on one of our business critical RAC > > databases with two instances was frequently leading into a dead lock > > situation ultimately a causing database hang scenario: > > > > SELECT u.u_user_id, u..u_mcr_cust_id > > FROM table1 U,table2 A > > WHERE > > A.UCS_CHNL_ID = :b1 AND A.UCS_LOGIN_NAME = :b2 AND A.UCS_USER_ID = > > U.U_USER_ID FOR UPDATE > > > > execution plan as follows: > > > > | Id | Operation | Name | > > Rows | Bytes | Cost (%CPU)| Time | > > > --------------------------------------------------------------------------------------------------------------- > > | 0 | SELECT STATEMENT | > > | | | 3 (100)| | > > > --------------------------------------------------------------------------------------------------------------- > > | 1 | FOR UPDATE | > > | | | | | > > | 2 | NESTED LOOPS | | > > 1 | 43 | 3 (0)| 00:00:01 | > > | 3 | TABLE ACCESS BY INDEX ROWID| table1 | 1 | 27 | > > 2 (0)| 00:00:01 | > > | 4 | INDEX UNIQUE SCAN | USER_CHANNEL_SUBSCRIPTION_FK4 | > > 1 | | 1 (0)| 00:00:01 | > > | 5 | TABLE ACCESS BY INDEX ROWID| table1 | > > 11883 | 185K| 1 (0)| 00:00:01 | > > | 6 | INDEX UNIQUE SCAN | table1_PK | > > 1 | | 0 (0)| | > > > --------------------------------------------------------------------------------------------------------------- > > > > -- > > Best Regards, > > > > Syed Jaffar Hussain > > > > -- > Sent from my mobile device > > Niall Litchfield > Oracle DBA > http://www.orawin.info > -- Best Regards, Syed Jaffar Hussain Oracle Certified Master (10g) http://www.oracle.com/technology/ocm/shussain.html Oracle ACE http://apex.oracle.com/pls/otn/f?p=19297:4:4640302666204919::NO:4:P4_ID:186 OCP 8i,9i & 10g DBA RAC Certified Expert Official Oracle RAC SIG Representative for Saudi Arabian region ( http://www.oracleracsig.org/) I blog at http://jaffardba.blogspot.com/ LinkedIn : http://www.linkedin.com/pub/syed-jaffar-hussain/2/a71/918 -------------------- "Winners don't do different things. They do things differently."