Enq Tx - Row Lock Contention HOWTO

最新推荐文章于 2022-08-22 20:10:05 发布

最新推荐文章于 2022-08-22 20:10:05 发布 · 166 阅读

ORACLE 专栏收录该内容

24 篇文章

订阅专栏

本文档提供了一套详细的步骤来收集与Oracle数据库BRM对象上出现高行锁竞争相关的信息。具体包括运行特定SQL查询获取会话详情、锁定对象等；收集AWR报告及其他有用的会话信息；记录所有坚持同一数据库的进程、客户端等；从所有CM进程中获取pstack；检查cm.pinlog和dm_oracle.pinlog文件。

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