共計 2463 個字符，預計需要花費 7 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下數(shù)據(jù)庫中如何查看歷史會話等待事件對應的 session 信息，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

此處以 enq: TX – row lock contention 等待時間為例。

如果在此回話發(fā)生在 awr 快照信息默認的保存天數(shù)以內(nèi)。
可以通過如下 sql 查詢到相關(guān)的 session 信息。
select * from DBA_HIST_ACTIVE_SESS_HISTORY where event like %enq: TX – row lock contention%

DBA_HIST_ACTIVE_SESS_HISTORY 中的 blocking_session 字段關(guān)聯(lián) DBA_HIST_ACTIVE_SESS_HISTORY 中的 session_id 找到對應的 sql_id 從而得到回話信息。

可以通過如下查詢直接獲取信息：
select t.instance_number,
       t.sample_time,
       lpad(– , 2 * (level – 1), – ) || t.client_id,
       t.session_id,
       t.blocking_session,
       t.session_serial#,
       t.sql_id,
       t.event,
       t.session_state,
       level,
       connect_by_isleaf,
       connect_by_iscycle
  from dba_hist_active_sess_history  t
 where snap_id between 36878 and 36879
 start with blocking_session is not null
        and event like enq: TX – row lock contention%
connect by nocycle sample_time = prior sample_time
       and session_id = prior blocking_session
       and session_serial# = prior blocking_session_serial#

其中 blocking session 為正在阻塞該回話的 session

實戰(zhàn)案例：
查看等待事件為行鎖的 session
select a.snap_id,
       a.sql_id,
       a.session_id,
       a.session_serial#,
       a.blocking_session,
       a.blocking_session_serial#,
       a.blocking_session_status
  from DBA_HIST_ACTIVE_SESS_HISTORY a
 where event like %enq: TX – row lock contention%
   and snap_id between 20399 and 20400

編寫子查詢，查看阻塞回話，并統(tǒng)計阻塞次數(shù)
select a.blocking_session,
       a.blocking_session_serial#,
       count(a.blocking_session)
  from DBA_HIST_ACTIVE_SESS_HISTORY a
 where event like %enq: TX – row lock contention%
   and snap_id between 20399 and 20400
 group by a.blocking_session, a.blocking_session_serial#
 order by 3 desc
 
查看阻塞回話的 sql_id 和被阻塞的 sql_id，條件為阻塞大于 19 次的
select distinct b.sql_id,c.blocked_sql_id
  from DBA_HIST_ACTIVE_SESS_HISTORY b,
       (select a.sql_id as blocked_sql_id,
       a.blocking_session,
               a.blocking_session_serial#,
               count(a.blocking_session)
          from DBA_HIST_ACTIVE_SESS_HISTORY a
         where event like %enq: TX – row lock contention%
           and snap_id between 20399 and 20400
         group by a.blocking_session, a.blocking_session_serial#,a.sql_id
        having count(a.blocking_session) 19
         order by 3 desc) c
 where b.session_id = c.blocking_session
   and b.session_serial# = c.blocking_session_serial#
   and b.snap_id between 20399 and 20400

動態(tài)性能視圖注釋：

V$ACTIVE_SESSION_HISTORY displays sampled session activity in the database. It contains snapshots of active database sessions taken once a second. A database session is considered active if it was on the CPU or was waiting for an event that didn t belong to the Idle wait class. Refer to the V$EVENT_NAME view for more information on wait classes.

看完了這篇文章，相信你對“數(shù)據(jù)庫中如何查看歷史會話等待事件對應的 session 信息”有了一定的了解，如果想了解更多相關(guān)知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝各位的閱讀！