Oracle 19c如何查询特定异常历史阻塞会话的详细活动信息

问题背景 #

当前使用Oracle Database 19c数据库，需要定位曾阻塞近50个会话的历史阻塞会话详细信息。在ASH报告对应的dba_hist_active_sess_history视图中，仅在blocking_session列查询到阻塞会话的SID为1258，但该SID并未出现在sid列中，现象异常。同时在hang analyze报告中也未找到该阻塞会话的相关活动信息，需确认除ASH之外，深度下钻分析特定会话历史活动详情的方法。

已获取的查询结果 #

dba_hist_active_sess_history输出 #

SAMPLE_ID  SAMPLE_TIME  SID  STATE    EVENT                      SQL_ID          BLK_SID  START_TIME  SQL_EXEC_ID
---------- ------------ ---- -------- -------------------------- --------------- -------- ----------- -----------
135345711  21 11:25:11  217  WAITING  enq: TX - row lock conten  shd23fhjdgjyhu  1258     21:04       19783669

注：BLK_SID 1258为查询到的阻塞会话SID，未在视图的SID列中出现对应记录

Hang analyze输出结果 #

{
                      p1: 'driver id'=0x54435000
                      p2: '#bytes'=0x1
            time in wait: 2 min 16 sec
           timeout after: never
                 wait id: 1445
                blocking: 1 session
          current sql_id: 3598363420
}

排查方案 #

首先明确SID 1258未出现在ASH的SID列属于Oracle正常机制：ASH仅每秒采样处于活跃状态（正在执行SQL、等待非空闲事件）的会话，若阻塞会话持有锁后处于空闲状态（等待客户端输入、网络无响应、事务挂起无操作），不会被ASH采样记录。可通过以下路径溯源：

• 关联AWR历史会话视图补全会话身份
  先通过AWR留存的历史会话元数据定位SID 1258对应的序列号、用户名、登录来源等基础信息，执行如下查询：

  SELECT 
      s.snap_id,
      s.instance_number,
      s.sid,
      s.serial#,
      s.username,
      s.program,
      s.machine,
      s.port,
      s.logon_time,
      s.status,
      s.prev_sql_id,
      s.sql_id as last_running_sql_id,
      sn.begin_interval_time,
      sn.end_interval_time
  FROM dba_hist_snapshot sn, dba_hist_sessstat s
  WHERE s.sid = 1258
    AND s.snap_id = sn.snap_id
    AND s.instance_number = sn.instance_number
    AND sn.begin_interval_time >= TO_DATE('2024-XX-21 20:30:00','YYYY-MM-DD HH24:MI:SS') -- 替换为阻塞发生前时间点
    AND sn.end_interval_time <= TO_DATE('2024-XX-21 12:00:00','YYYY-MM-DD HH24:MI:SS') -- 替换为阻塞结束后时间点
  ORDER BY sn.begin_interval_time DESC;
  

  dba_hist_sessstat会留存AWR保留期内所有会话的统计快照，不受会话活跃/空闲状态限制，可通过该视图拿到阻塞会话的唯一标识serial#，确认会话来源端属性。

• 通过事务历史视图定位锁持有记录
  TX行锁阻塞的本质是持有锁的事务未提交/回滚，可关联AWR历史事务视图直接查询锁对应的事务信息：

  SELECT 
      t.snap_id,
      t.session_id,
      t.ses_serial#,
      t.xid,
      t.start_time as trans_start_time,
      t.end_time as trans_end_time,
      t.log_io,
      t.phy_io,
      s.sql_text as last_exec_sql,
      u.name as locked_object_owner,
      o.name as locked_object_name
  FROM dba_hist_active_sess_history h, dba_hist_transaction t, dba_hist_sqltext s, sys.obj$ o, sys.user$ u
  WHERE h.blocking_session = 1258
    AND h.sample_id = 135345711 -- 替换为阻塞发生时的SAMPLE_ID
    AND t.session_id = h.blocking_session
    AND t.start_time <= h.sample_time
    AND NVL(t.end_time, SYSDATE) >= h.sample_time
    AND t.sql_id = s.sql_id(+)
    AND t.object_id = o.obj#(+)
    AND o.owner# = u.user#(+);
  

  该查询可直接获取阻塞会话持有的未提交事务启动时间、最后执行的SQL、锁定的具体业务对象。

• 通过审计日志溯源会话全量操作
  若数据库开启了标准审计或19c默认的统一审计，可直接查询审计记录获取会话的所有操作轨迹：

  -- 标准审计记录查询
  SELECT sessionid, entryid, sql_text, action_name, event_timestamp, userhost, terminal
  FROM dba_audit_trail
  WHERE sessionid IN (
      SELECT audit_sessionid FROM dba_hist_sessstat 
      WHERE sid=1258 AND snap_id = (SELECT max(snap_id) FROM dba_hist_snapshot WHERE begin_interval_time < TO_DATE('2024-XX-21 11:25:11','YYYY-MM-DD HH24:MI:SS'))
  )
  ORDER BY event_timestamp;
  
  -- 统一审计记录查询（19c默认开启）
  SELECT session_id, sql_text, action_name, event_timestamp, client_hostname, client_program_name
  FROM unified_audit_trail
  WHERE session_id IN (
      SELECT audit_sessionid FROM dba_hist_sessstat 
      WHERE sid=1258 AND snap_id = (SELECT max(snap_id) FROM dba_hist_snapshot WHERE begin_interval_time < TO_DATE('2024-XX-21 11:25:11','YYYY-MM-DD HH24:MI:SS'))
  )
  ORDER BY event_timestamp;
  

• 通过监听日志匹配连接来源
  若以上视图留存信息不足，可通过SID匹配监听日志（路径为$ORACLE_BASE/diag/tnslsnr/[主机名]/listener/trace/listener.log）中的连接记录，确认会话来源IP、精确连接时间，结合应用端服务日志定位当时执行的业务操作。

• Hang analyze结果补充说明
  提供的hang analyze片段中p1值0x54435000转ASCII为TCP，说明被阻塞会话当时处于TCP网络消息等待状态，阻塞链末端的会话已出现网络无响应特征，不会被ASH作为活跃会话采样，和之前观察到的SID未出现在ASH列的现象完全吻合。

内容的提问来源于stack exchange，提问作者Kishan