Re: Which query is best?
Date: Tue, 15 Dec 2009 16:00:53 +0300
Message-ID: <97b7fd2f0912150500s46afc143k2430cf1aa59d816f_at_mail.gmail.com>
Rajaram,
Thanks for your suggestions and link. However, we can't implement SKIP LOCKED option because of the below reason given in the link which you provided:
>>This feature is useful if the goal of the query is to obtain numbers of units, rather than the actual content of the rows.
As we are selecting the actual content of the rows rather than obtaining number of units.
On Tue, Dec 15, 2009 at 2:10 PM, Rajaram Subramanian < rajaram.subramanian_at_yahoo.com> wrote:
> Hi Syed,
>
> You could try the same query with the following option.
>
> select ... from <table_name> for update skip locked;
>
> It's documented in 11g and it is undocumented in 10g.
>
>
> http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#i2066346
>
> Regards
>
> Raj
>
> --- On *Tue, 15/12/09, Syed Jaffar Hussain <sjaffarhussain_at_gmail.com>*wrote:
>
>
> From: Syed Jaffar Hussain <sjaffarhussain_at_gmail.com>
> Subject: Which query is best?
> To: "Oracle-L Freelists" <oracle-l_at_freelists.org>
> Date: Tuesday, 15 December, 2009, 7:22
>
>
> 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
>
>
>
-- 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-lReceived on Tue Dec 15 2009 - 07:00:53 CST