-
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 = :
When in doubt ...go to the basics!!
-
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
When in doubt ...go to the basics!!
-
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.
When in doubt ...go to the basics!!
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
When in doubt ...go to the basics!!
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|