High and short enqueue waits

[bhatia333]

Hi Gurus,
I am facing an issue with one of the applications which has very high enqueue waits showing up in the AWR report. When I checked it through a scirpt, I can see that the holding session is changing very quickly which means that the lock itself is held for a very short time but there are too many sessions which are getting and releasing the locks. Killing the session wont be an option. What are my options here.

DB Details:
Version 10.2.0.4
OS Windows Server 2003 R2

Output from the script:
SESS                        ID1        ID2      LMODE    REQUEST TY    INST_ID SQL_TEXT
-------------------- ---------- ---------- ---------- ---------- -- ---------- ----------------------------------------------------------------------------------------------------
Holder: 120                  10   37750683          6          0 HW          1 UPDATE T_CKC_RUNTIME          SET ckc_blob =  :BP_00002_v:BP_00002_i, first_sample_date =  :
Waiter: 117                  10   37750683          0          6 HW          1 UPDATE T_CKC_RUNTIME          SET ckc_blob =  :BP_00002_v:BP_00002_i, first_sample_date =  :

[bhatia333]

Please do let me know if there is any detail missing. I do understand that as a DBA there is very little that we can do for application design issues which cause locking. But this is kinda urgent and we need to resolve this issue. Any pointers in things to look at would be of great help.

Thanks

[bhatia333]

Hi All,
I have not seen any response. I tried searching on this forum but couldnt find anything which is relevant in my case. Please do let me know if there are other details required.

[PAVB]

It appears to be HW enqueue contention on LOB.
You may want to check bug #6376915 in Metalink and see how well it fits what you are seeing.
Hope this helps.

[bhatia333]

Hi Pablo,
Thanks for your response. I apologize for pasting the wrong output. I am aware of the bug and we are going to put a fix for it. But there is also very short TX type locks which occur. Wanted to know how to resolve those.

Holding SID: 115

Lock Mode: 6

Request Mode: 0

Request Type: TX

Waiting (sec): 0

SQL Text: UPDATE T_CKC_RUNTIME                  SET ckc_blob =  :BP_00002_v:BP_0

0002_i, first_sample_date =  :BP_00003_v:BP_00003_i, last_sample_date =  :BP_000

 

 

Kill command (sqlplus): alter system kill session '115,20';

 

Waiting SID: 125

Lock Mode: 0

Request Mode: 6

Request Type: TX

Waiting (sec): 32

SQL Text: SELECT /*+ NOPARALLEL (t_ckc_runtime)*/ r.ch_id, r.ckc_id, r.folder_id

, r.fxs_available, r.min_sample_size, r.max_sample_size, r.ckc_blob, t.reset_dat

[PAVB]

So... you changed the question after getting the answer? bad mojo LOL

TXs are transaction locks, nothing to be resolved there. You process transactions, you generate TXs or to say it in a different way, if you get rid of all your users and all your scheduled jobs you will be TX free Not sure if business will buy the plan.