Re: Which query is best?

From: Rajaram Subramanian <rajaram.subramanian_at_yahoo.com>
Date: Tue, 15 Dec 2009 03:10:30 -0800 (PST)
Message-ID: <936866.72294.qm_at_web24104.mail.ird.yahoo.com>

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




      
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 15 2009 - 05:10:30 CST