This content has been marked as final. Show 3 replies
Steve -1 person found this helpful
We were receiving some of the same problems and us developers had a heck of a time trying to figure out which object was actually the one with the lock on it. We would run different queries and see things like you were reporting. Then our DBA turned us onto Oracle Enterprise Manager Grid Control. We can navigate to the particular database and monitor any "Instance Locks - Blocking Locks". I'm sure you could run a SQL to see this same thing, but of course they don't tell you what that SQL is. I did some Googling and I came up with a query based on lots of different queries that seems be close to what Grid Control shows when a lock occurs. I don't guarantee anything with this query but here it is:
The next time you get a lock, run this query and see what table shows up in the OBJECT_NAME column. That will be your culprit if this SQL is correct and that is a big if... You would be better off talking to your DBA and seeing if they can give you access to Grid Control (if it is installed).
SELECT L2.USERNAME, L2.SID, L2.SERIAL#, L2.MODE_HELD, L2.MODE_REQUESTED, O.name OBJECT_NAME FROM (SELECT /*+ ordered */ 0, S.sid, S.serial#, NVL (S.sql_id, 0), DECODE (L.TYPE, 'TM', L.id1, 'TX', DECODE (L.request, 0, NVL (LO.object_id, -1), S.row_wait_obj# ), -1 ) AS object_id, S.username, S.row_wait_obj#, S.row_wait_block#, S.row_wait_row#, S.row_wait_file#, L.TYPE, DECODE (L.lmode, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?' ) MODE_HELD, DECODE (L.request, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?' ) MODE_REQUESTED, L.id1, L.id2, L.ctime, P.spid, S.sql_hash_value FROM v$lock L, v$session S, v$process P, (SELECT object_id, session_id, xidsqn FROM v$locked_object WHERE xidsqn > 0) LO WHERE S.sid = L.sid AND P.addr = S.paddr AND L.TYPE != 'MR' AND L.sid = LO.session_id(+) AND L.id2 = LO.xidsqn(+)) L2, sys.obj$ O, sys.user$ U WHERE O.obj#(+) = L2.object_id AND O.owner# = U.user#(+) AND O.name is not null AND L2.sid in ( SELECT vh.sid FROM v$lock vh, v$lock vw, v$session vs, v$session vsw WHERE (vh.id1, vh.id2) IN (SELECT id1, id2 FROM v$lock WHERE request = 0 INTERSECT SELECT id1, id2 FROM v$lock WHERE lmode = 0) AND vh.id1 = vw.id1 AND vh.id2 = vw.id2 AND vh.request = 0 AND vw.lmode = 0 AND vh.sid = vs.sid AND vw.sid = vsw.sid)
We moved to 11g recently and were experiencing many more locks than usual. One of our developers found something that said that the locking was changed in 11g which was causing us a problem. I hope I can explain this right, but if you have a PK-FK relationship and the FK column is not indexed then the PK table is locked until the user commits or does a rollback. Once we had this bit of information, everytime there was a lock we would look at Grid Control (or hopefully this query) and see which object was locked. We would then check the FKs in that table and if any of them contained non-indexed columns that referenced the PK we would index them and the problems went away.
Hope this info helps.
Wow. This sounds amazingly helpful. I will take your advice and look into the FK non-indexed columns referring to a PK. Thanks so much.
Tom Kyte provided a script to search for unindexed foreign keys
and an article from Oracle Magazin