会话阻塞时dba_hist_active_sess_history的time_waited字段显示异常

先创建用户并授权：

SQL> CREATE USER DEMO IDENTIFIED BY DEMO DEFAULT TABLESPACE USERS;

User created.

SQL> GRANT CONNECT, RESOURCE TO DEMO;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO DEMO;

Grant succeeded.

第一个会话执行操作：

SQL> CONNECT DEMO/DEMO
Connected.
SQL> CREATE TABLE D_LOCK(DIGIT NUMBER, STRING VARCHAR(10));
Table created.

INSERT INTO D_LOCK VALUES (1, 'ONE');
SQL> 
1 row created.

INSERT INTO D_LOCK VALUES (2, 'TWO');
SQL> 
1 row created.

SQL> COMMIT;
Commit complete.

SQL> UPDATE D_LOCK SET STRING='001' WHERE DIGIT=1;
1 row updated.

第二个会话执行删除操作后挂起：

SQL> CONNECT DEMO/DEMO
Connected.

SQL> delete from D_LOCK WHERE DIGIT=1;

第二个会话因等待第一个会话提交而挂起

数秒后执行以下ASH查询：

SELECT
    ash.snap_id,
    ash.sample_time,
    ash.blocking_session,
    ash.blocking_session_serial#,
     ' -> '                    as is_blocking,
    ash.session_id as blocked_sid,
    ash.session_serial# as blocked_serial,
    ash.sql_id as blocked_sql_id,
    sql.sql_text as blocked_sql_text,
    ash.sql_opname as blocked_sql_opname,
    ash.event as blocked_event,
    round(ash.time_waited / 1000000) AS blocked_sec
FROM
    dba_hist_active_sess_history   ash,
    v$sql                          sql
WHERE
    blocking_session IS NOT NULL
    AND ash.sql_id = sql.sql_id 
    and BLOCKING_SESSION_STATUS = 'VALID'
    --and time_waited>0 
ORDER BY
    sample_id DESC;

但查询结果中blocked_sec字段值为0，明明会话等待了数秒，这是为什么？

• ASH的采样机制限制
  DBA_HIST_ACTIVE_SESS_HISTORY是内存视图V$ACTIVE_SESSION_HISTORY的历史归档表，默认采样频率为每10秒一次。如果你的会话等待时长不足10秒，或者采样时刻刚好没捕捉到该会话的等待状态，就会出现time_waited为0的情况，甚至可能根本没有这条等待记录。

• TIME_WAITED字段的定义
  ASH中的TIME_WAITED代表当前采样周期内会话处于等待状态的时间，并非从等待开始到结束的累计时长。如果采样时会话刚进入等待，或者在采样周期内等待时间未达到统计阈值，就会显示为0。

• 改用实时视图获取准确等待时长
  若要查看实时的累计等待时间，应该查询V$SESSION_WAIT或V$SESSION这类实时视图，示例查询：

  SELECT
      s.sid AS blocked_sid,
      s.serial# AS blocked_serial,
      sw.event AS blocked_event,
      sw.seconds_in_wait AS blocked_sec
  FROM
      v$session s
  JOIN v$session_wait sw ON s.sid = sw.sid
  WHERE
      s.blocking_session IS NOT NULL
      AND s.status = 'ACTIVE';
  

  这个查询能直接获取会话当前的累计等待秒数，不受ASH采样间隔的限制。

• ASH数据的存储条件
  ASH只会记录活跃等待状态的会话，且只有被采样到的会话才会被写入历史表。如果等待时间过短，或者采样间隔内会话已经结束等待（比如你在查询前已经提交了第一个会话的事务），就不会生成有效等待时长的记录。

内容的提问来源于stack exchange，提问作者Рязанский Крестоносец