Which query is best?

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 |

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