记enq: HW - contention等待事件与表空间自动扩展引发的故障

一、故障现象

　　在15:30-16:00 检测发现大量enq: HW - contention和enq: TX - row lock contention等待事件，并且这两事件越来越多，最终导致短暂的业务宕机。

后面检测发现uses表空间正在使用自动扩展，于是增加users表空间，问题解决

二、故障分析

　　1、查看15:30-16:00时间的snap_id，用于定位这个时间段内的所有session操作

　　

select * from dba_hist_snapshot where begin_interval_time>to_date('2019-12-03','yyyy-mm-dd')

　　2、查看这个时间段主要的等待事件

select 
       to_char(sample_time, 'YYYY-MM-DD HH24:MI'),event,
       count(*)
  from dba_hist_active_sess_history
 where snap_id in (116604, 116605)
 and sample_time>=to_date('2019-12-03 15:10','yyyy-mm-dd hh24:mi')
 group by  to_char(sample_time, 'YYYY-MM-DD HH24:MI'),event
 order by 1,3;

从上面分析可以看出等待事件为enq: HW – contention，enq: TX - row lock contention

 3、根据上面的等待事件查看哪些语句执行产生的（526awsta39934）

 

select t.instance_number,
        t.sample_time,
        t.session_id,
        t.session_serial#,
        t.sql_id,
        t.event,
        t.blocking_session,
        t.blocking_inst_id,
        t.program,
        t.machine
   from dba_hist_active_sess_history t
  where snap_id in (116604, 116605)
    and to_char(sample_time,'yyyy-mm-dd hh24:mi')='2019-12-03 15:39'
    and event in ('enq: TX - row lock contention', 'enq: HW - contention')
  order by 2, 1;

4、根据上面的sql_id为‘526awsta39934’引起的‘enq: HW - contention’高水位线争用事件，查看出语句为

INSERT INTO TRANSACTION_LOG VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,:B15 )

5、在根据步骤3，可以查出‘enq: TX - row lock contention’行锁的阻塞会话id

根据上面阻塞的会话id可以，查找正好是sql_id’ 526awsta39934’阻塞

select t.instance_number,
        t.sample_time,
        t.session_id,
        t.session_serial#,
        t.sql_id,
        t.event,
        t.blocking_session,
        t.blocking_inst_id,
        t.program,
        t.machine
   from dba_hist_active_sess_history t
  where snap_id in (116604, 116605)
    and /*sample_time >= to_date('2019-12-03 15:10', 'yyyy-mm-dd hh24:mi')
    and */to_char(sample_time,'yyyy-mm-dd hh24:mi')='2019-12-03 15:39'
    and t.session_id in (2531,3595,2128,3208,3555,5598,168)
    and t.instance_number=2
   -- and event in ('enq: TX - row lock contention', 'enq: HW - contention')
  order by 2, 1;

6、总结

由上面可以分析当大量高并发语句‘526awsta39934’，往数据库中插入数据会去查看数据块高水位线下的可用块，正好此时表空间users处于自动扩展，同时其他的会话也在插入数据或修改数据，也需要高水位线下的可用块。最终引起了行锁。

 

ps:关于oracle高水位线参考

https://www.cnblogs.com/linjiqin/archive/2012/01/15/2323030.html