Re: Which query is best?
Date: Tue, 15 Dec 2009 19:52:12 +0300
Message-ID: <97b7fd2f0912150852s198f0a6bwc087451ef0857f1f_at_mail.gmail.com>
The piece of information was straight from the Oracle documentation and I am sure that it is not so clear with the contents. What I understood from this line was that SKIP LOCK is good when you have a query returning the count, sum or etc instead of an entire row contents. However, Rob gave a good explanation.
On Tue, Dec 15, 2009 at 7:35 PM, Rajaram Subramanian < rajaram.subramanian_at_yahoo.com> wrote:
> Hi Syed,
>
> To be honest I am not able to figure out what this line means.
>
> "This feature is useful if the goal of the query is to obtain numbers of
> units, rather than the actual content of the rows."
>
> You could see a working example on this post.
>
> http://rwijk.blogspot.com/2009/02/for-update-skip-locked.html
>
> Hope this helps.
>
> Regards
>
> Raj
>
>
> --- On *Tue, 15/12/09, Syed Jaffar Hussain <sjaffarhussain_at_gmail.com>*wrote:
>
>
> From: Syed Jaffar Hussain <sjaffarhussain_at_gmail.com>
> Subject: Re: Which query is best?
> To: "Rajaram Subramanian" <rajaram.subramanian_at_yahoo.com>
> Cc: "Oracle-L Freelists" <oracle-l_at_freelists.org>
> Date: Tuesday, 15 December, 2009, 13:00
>
>
> 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<http://uk.mc241.mail.yahoo.com/mc/compose?to=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<http://uk.mc241.mail.yahoo.com/mc/compose?to=sjaffarhussain_at_gmail.com>
>> >* wrote:
>>
>>
>> From: Syed Jaffar Hussain <sjaffarhussain_at_gmail.com<http://uk.mc241.mail.yahoo.com/mc/compose?to=sjaffarhussain_at_gmail.com>
>> >
>> Subject: Which query is best?
>> To: "Oracle-L Freelists" <oracle-l_at_freelists.org<http://uk.mc241.mail.yahoo.com/mc/compose?to=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."
>
>
>
-- 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 - 10:52:12 CST