High and short enqueue waits
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: High and short enqueue waits

  1. #1
    Join Date
    Aug 2002
    Posts
    56

    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!!

  2. #2
    Join Date
    Aug 2002
    Posts
    56
    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!!

  3. #3
    Join Date
    Aug 2002
    Posts
    56
    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!!

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  5. #5
    Join Date
    Aug 2002
    Posts
    56
    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!!

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
  •  


Click Here to Expand Forum to Full Width