查看引起"TX - row lock contention"的语句 - abce

查看引起"TX - row lock contention"的语句

2022-03-23 16:14 abce 阅读(75) 评论(0) 编辑收藏举报

#当前会话中查看引起行锁竞争的语句

select sw.event,
       sw.sid,
       sw.p1,
       sw.p2,
       sw.p3,
       s.ROW_WAIT_OBJ#,
       s.ROW_WAIT_FILE#,
       s.ROW_WAIT_BLOCK#,
       s.ROW_WAIT_ROW#,
       o.OWNER,
       o.OBJECT_NAME,
       o.OBJECT_ID,
       o.DATA_OBJECT_ID,
       o.OBJECT_TYPE,
       st.sql_id,
       st.sql_text
  from v$session_wait sw, v$session s, dba_objects o, v$sql st
 where sw.sid = s.sid
   and o.object_id = s.ROW_WAIT_OBJ#
   and (st.sql_id = s.sql_id or st.sql_id = s.prev_sql_id)
   and sw.event = 'enq: TX - row lock contention';

#从历史会话中查看引起行锁竞争的语句

select ash.sample_time,
       ash.instance_number,
       ash.user_id,
       u.username,
       ash.session_id,
       ash.session_serial#,
       ash.current_obj#,
       o.owner,
       o.object_name,
       o.object_type,
       ash.sql_id,
       ash.sql_opname,
       ash.wait_class,
       ash.program,
       ash.module,
       ash.blocking_session_status,
       ash.blocking_session,
       ash.blocking_session_serial#,
       ash.blocking_inst_id,
       st.inst_id,
       st.sql_text
  from dba_hist_active_sess_history ash,
       dba_users                    u,
       dba_objects                  o,
       gv_$sql                      st
 where to_char(ash.sample_time, 'YYYY-MM-DD hh24:mi:ss') between  '2022-03-22 13:30:00' and '2022-03-22 15:30:00'
   and ash.time_waited > 0
   and ash.session_state = 'WAITING'
   and ash.user_id = u.user_id
   and ash.current_obj# = o.object_id
   and st.sql_id = ash.sql_id
   and ash.event = 'enq: TX - row lock contention';