查看前端对应的sql脚本_分享几个实用脚本--查看历史会话等待事件对应的session信息...

最新推荐文章于 2023-12-12 09:50:43 发布

weixin_39523887

最新推荐文章于 2023-12-12 09:50:43 发布

阅读量273

点赞数

文章标签：查看前端对应的sql脚本

概述

今天主要介绍下怎么查看历史会话等待事件对应的session信息。

以下基于Oracle11.2.0.4版本。

思路：

以enq: TX - row lock contention等待时间为例。通过如下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从而得到会话信息。

注意此会话需发生在awr快照信息默认的保存天数以内。

1、查看awr时间段

这里选择快照时间段为58525到58569之间。

select to_char(s.startup_time, 'dd Mon "at" HH24:mi:ss') instart_fmt, di.instance_name inst_name, di.db_name db_name, s.snap_id snap_id, to_char(s.end_interval_time, 'dd Mon YYYY HH24:mi') snapdat, s.snap_level lvl from dba_hist_snapshot s, dba_hist_database_instance di where di.dbid = s.dbid and di.instance_number = s.instance_number and di.startup_time = s.startup_time order by db_name, instance_name, snap_id;

2、查看等待事件为行锁的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 58525 and 58569;

3、查看阻塞会话，并统计阻塞次数

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 58525 and 58569 group by a.blocking_session, a.blocking_session_serial# order by 3 desc

阻塞会话466总共阻塞了2152次

4、查看阻塞会话的sql_id和被阻塞的sql_id，条件为阻塞大于19次的

select distinct b.sql_id,c.blocked_sql_id,b.session_id,b.session_serial#,b.sql_opname,b.event,b.module,b.program,b.machine 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 58525 and 58569 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 58525 and 58569;