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@xxxxxxxxx> 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@xxxxxxxxx>*wrote: > > > From: Syed Jaffar Hussain <sjaffarhussain@xxxxxxxxx> > Subject: Re: Which query is best? > To: "Rajaram Subramanian" <rajaram.subramanian@xxxxxxxxx> > Cc: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx> > 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@xxxxxxxxx<http://uk.mc241.mail.yahoo.com/mc/compose?to=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<http://uk.mc241.mail.yahoo.com/mc/compose?to=sjaffarhussain@xxxxxxxxx> >> >* wrote: >> >> >> From: Syed Jaffar Hussain >> <sjaffarhussain@xxxxxxxxx<http://uk.mc241.mail.yahoo.com/mc/compose?to=sjaffarhussain@xxxxxxxxx> >> > >> Subject: Which query is best? >> To: "Oracle-L Freelists" >> <oracle-l@xxxxxxxxxxxxx<http://uk.mc241.mail.yahoo.com/mc/compose?to=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." > > > -- 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."