High and short enqueue waits

Printable View

10-11-2010, 01:44 PM
bhatia333

High and short enqueue waits

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

Code:

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 = :
10-11-2010, 04:29 PM
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
10-12-2010, 03:56 PM
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.
10-12-2010, 04:13 PM
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.
10-13-2010, 01:54 PM
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.

Code:

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
10-13-2010, 03:51 PM
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.