数据库中如何查看历史会话等待事件对应的session信息

小编给大家分享一下数据库中如何查看历史会话等待事件对应的session信息，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！
如果在此回话发生在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.b免费主机域名locking_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

动态性能视图注释：
看完了这篇文章，相信你对“数据库中如何查看历史会话等待事件对应的session信息”有了一定的了解，如果想了解更多相关知识，欢迎关注云技术行业资讯频道，感谢各位的阅读！

相关推荐: MySQL如何实现用户密码过期功能

这篇文章主要介绍了MySQL如何实现用户密码过期功能，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 从MySQL版本5.6.6版本起，添加了password_expired功能，它允许设置用户的…