Re: Which query is best?
- From: Syed Jaffar Hussain <sjaffarhussain@xxxxxxxxx>
- To: Rajaram Subramanian <rajaram.subramanian@xxxxxxxxx>
- Date: Tue, 15 Dec 2009 16:00:53 +0300
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@xxxxxxxxx> 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@xxxxxxxxx>*wrote:
>
>
> From: Syed Jaffar Hussain <sjaffarhussain@xxxxxxxxx>
> Subject: Which query is best?
> To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
> 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."
Other related posts:
