Which query is best?

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

Other related posts: