Enq Tx - Row Lock Contention HOWTO

Troubleshooting Details

List of information to collect when high Row-Lock contention is seen on BRM DB objects (i.e. "Enq: Tx - Row Lock Contention In AWR Reports" messages seen in AWR reports)

1) output of the following two queries:

a)

SELECT a.sid||decode(request,0,'(holder)','(waiter)'||':blocked by:'||blocking_session) sess_id ,
lmode,
request,
a.type,
c.object_name,
decode(row_wait_obj#,-1,'Holder of Lock !!!',
dbms_rowid.rowid_create(1,row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#)) row_id,
nvl(SQL_FULLTEXT,'Holder of Lock !!!')
FROM V$LOCK A, V$LOCKED_OBJECT B, ALL_OBJECTS C, V$SESSION D, V$SQL E
WHERE (id1, id2, a.type) in (select id1, id2, type from v$lock where request>0)
AND a.sid = b.session_id
AND b.object_id = c.object_id
AND d.sid = a.sid
AND d.sql_hash_value = e.hash_value(+)

b)

select * from <object_name> where rowid = <row_id> [ after getting object name and row_id from a) ]

2) the AWR reports, or any other useful collection of session info from BRM DB

3) the list of all processes, clients, etc. insisting on the same DB

4) the "pstack" taken on all CM processes

5) the cm.pinlog and dm_oracle.pinlog