Locking issue with production database

Printable View

05-18-2009, 02:08 AM
mahajanakhil198

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)??
05-18-2009, 07:09 AM
marist89

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.
05-18-2009, 01:49 PM
LKBrwn_DBA

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).
;)
05-19-2009, 10:35 AM
mahajanakhil198

Quote:

Originally Posted by marist89

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

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