Re: Which query is best?

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 15 Dec 2009 07:57:58 +0000
Message-ID: <7765c8970912142357w66ef6333k49703510176716d7_at_mail.gmail.com>

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
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 15 2009 - 01:57:58 CST