檢視歷史會話等待事件對應的session資訊

kisslfcr發表於2017-09-12
會話事件Session

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


如果在此回話發生在awr快照資訊預設的儲存天數以內。
可以透過如下sql查詢到相關的session資訊。
select * from DBA_HIST_ACTIVE_SESS_HISTORY where event like '%enq: TX - row lock contention%'

DBA_HIST_ACTIVE_SESS_HISTORY 中的blocking_session欄位關聯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


實戰案例：
檢視等待事件為行鎖的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


編寫子查詢，檢視阻塞回話，並統計阻塞次數
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

動態效能檢視註釋：

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.

這個檢視三思老師的部落格裡將的比較清楚：
http://blog.itpub.net/7607759/viewspace-617178

This view contains one row for each active session per sample and returns the latest session sample rows first. A majority of the columns describing the session in the active session history are present in the V$SESSION view.

DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history of recent system activity. This view contains snapshots of V$ACTIVE_SESSION_HISTORY. See "V$ACTIVE_SESSION_HISTORY" for further interpretation details for many of these columns (except SNAP_ID, DBID, and INSTANCE_NUMBER).

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29135257/viewspace-2144876/，如需轉載，請註明出處，否則將追究法律責任。

相關文章