Re: Which query is best?

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

Other related posts: