DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Locking issue with production database

  1. 05-18-2009, 02:08 AM #1
    mahajanakhil198
    mahajanakhil198 is offline Senior Member
    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
    Reply With Quote Reply With Quote
  2. 05-18-2009, 07:09 AM #2
    marist89's Avatar
    marist89
    marist89 is offline Super-Genius
    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
    Reply With Quote Reply With Quote
  3. 05-18-2009, 01:49 PM #3
    LKBrwn_DBA's Avatar
    LKBrwn_DBA
    LKBrwn_DBA is offline Senior Advisor
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    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
    Reply With Quote Reply With Quote
  4. 05-19-2009, 10:35 AM #4
    mahajanakhil198
    mahajanakhil198 is offline Senior Member
    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
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

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