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)??