Re: Which query is best?

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_at_gmail.com> 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_at_gmail.com> 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."

--
http://www.freelists.org/webpage/oracle-l