Locking issue with production database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Locking issue with production database

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    Locking issue with production database

    I often see sessions causing locking in my database. When I drill down using hang analysis in performance tab of 10g database control enterprise control to determine the sessions blocking other sessions, I often see that the current or previous statement is usually select statement (it is not select for update). I am not able to figure out why these select statements block other sessions. It may be that any statement in PL/SQL block of this select statement is part of could have caused locking. But I am not much convinced with this perception of mine. So just thought of discussing here.

    I want advice on these two issues:
    1. Either I want to find the statement in PL/SQL block, of which select statement is part of, that caused the locks..
    2. Or the present value of open_cursors is 300. I have sufficient RAM to increase the value of this parameter as SGA_MAX_SIZE is 5G. In fact, I have already increased it to 1200. Could it be possible bottleneck earlier that might have caused some system level locking ( as I am not much aware of system level locks)??
    lucky

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You will never understand what's truly going on until you dump the GUI.

    Trace the process that is hanging. It most likely has nothing to do with SGA size or open_cursors.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459

    Cool The dumps

    And, you will also get a better picture if you take a look at the trace (dump) files created by the deadlock(s).
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by marist89 View Post
    You will never understand what's truly going on until you dump the GUI.

    Trace the process that is hanging. It most likely has nothing to do with SGA size or open_cursors.
    hmmm...i wud have loved tracing but how am i to know which session will block others until it really blocks others..N if i start tracing after it blocks others, I am not likely to get the statement that really caused locking...Isn't it?




    Quote Originally Posted by LKBrwn_DBA View Post
    And, you will also get a better picture if you take a look at the trace (dump) files created by the deadlock(s).
    It is only blocking other sessions and the problem corrects after 10-15 minutes....So how comes deadlock..
    lucky

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