Which query is best?
- From: Syed Jaffar Hussain <sjaffarhussain@xxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 15 Dec 2009 10:22:16 +0300
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:
