Re: Which query is best?

From: Rajaram Subramanian <rajaram.subramanian_at_yahoo.com>
Date: Tue, 15 Dec 2009 08:35:13 -0800 (PST)
Message-ID: <543683.77097.qm_at_web24104.mail.ird.yahoo.com>



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> 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-l
Received on Tue Dec 15 2009 - 10:35:13 CST

Original text of this message