RAC环境:
查看行争用的等待session
select inst_id,sid,seconds_in_wait from gv$session_wait where wait_class <>'Idle' and event='enq: TX - row lock contention'
查看阻塞与被阻塞的session
select inst_id,sid,taddr,lockwait,status,schemaname,sql_id,prev_sql_id,blocking_instance,blocking_session,seconds_in_wait from gv$session where (sid=1231 and inst_id=5) or (sid=821 and inst_id=3) or (sid=1011 and inst_id=4)
查看等待请求lock类型
select * from v$lock where kaddr='000000053CA10910'
查看enq: TX - row lock contention相关sql
select * from table(dbms_xplan.display_awr('bunvx480ynf57'));
select m.sql_fulltext,n.value_string from v$sql m,v$sql_bind_capture n where m.sql_id='6yhtsctmu6st0' and m.child_address=n.child_address;