Oracle中TX锁(行锁)监控,抓TX锁的源头

转载:https://blog.csdn.net/robinson1988/article/details/106204387

各位DBA,看到这篇文章是不是很开心,解决了你一个大麻烦,赶紧把它部署到实时监控程序吧

(咳咳,转载,抄袭不注明文章出处的人可耻哈)

session 1: update emp_bak set ename='沙雕' where empno=7369;

session 2: update emp_bak set ename='大长腿' where empno=7369;

session 3: update emp_bak set ename='矮丑穷' where empno=7369;

运行下面脚本可以抓到哪个SID,哪个SQL_ID,跑的SQL_TEXT锁住了哪个SID,哪个SQL_ID,哪个SQL语句

需要注意的是:如果V$SQLAREA没有保存SQL,可能抓不到,其次,如果系统并发很高,你可能需要再修改下脚本
 

select sysdate,
       source_sid,
       source_sql_id,
       source_sql_text,
       blocking_sid,
       blocking_sql_id,
       blocking_sql_text
  from (select b.sid source_sid,
               d.sql_id source_sql_id,
               d.sql_text source_sql_text,
               a.sid blocking_sid,
               a.sql_id blocking_sql_id,
               e.sql_text blocking_sql_text,
               (select object_name
                  from dba_objects
                 where object_id = a.row_wait_obj#) object_name
          from v$session     a,
               v$session     b,
               v$transaction c,
               v$sqlarea     d,
               v$sqlarea     e
         where a.event = 'enq: TX - row lock contention'
           and a.blocking_session = b.sid
           and b.taddr = c.addr
           and to_date(c.start_time, 'mm/dd/yy hh24:mi:ss') =
               d.last_active_time
           and d.command_type in (2, 3, 6)
           and b.user# = d.parsing_schema_id
           and a.sql_id = e.sql_id)
 where instr(upper(source_sql_text), object_name) > 0;

脚本运行示例:

SQL> select sysdate,
  2         source_sid,
  3         source_sql_id,
  4         source_sql_text,
  5         blocking_sid,
  6         blocking_sql_id,
  7         blocking_sql_text
  8    from (select b.sid source_sid,
  9                 d.sql_id source_sql_id,
 10                 d.sql_text source_sql_text,
 11                 a.sid blocking_sid,
 12                 a.sql_id blocking_sql_id,
 13                 e.sql_text blocking_sql_text,
 14                 (select object_name
 15                    from dba_objects
 16                   where object_id = a.row_wait_obj#) object_name
 17            from v$session     a,
 18                 v$session     b,
 19                 v$transaction c,
 20                 v$sqlarea     d,
 21                 v$sqlarea     e
 22           where a.event = 'enq: TX - row lock contention'
 23             and a.blocking_session = b.sid
 24             and b.taddr = c.addr
 25             and to_date(c.start_time, 'mm/dd/yy hh24:mi:ss') =
 26                 d.last_active_time
 27             and d.command_type in (2, 3, 6)
 28             and b.user# = d.parsing_schema_id
 29             and a.sql_id = e.sql_id)
 30   where instr(upper(source_sql_text), object_name) > 0;
 
SYSDATE     SOURCE_SID SOURCE_SQL_ID SOURCE_SQL_TEXT                                  BLOCKING_SID BLOCKING_SQL_ID BLOCKING_SQL_TEXT
----------- ---------- ------------- ------------------------------------------------ ------------ --------------- ------------------------------------------------
2020/5/19 1        192 201c4xcdsjaj0  update emp_bak set ename='沙雕' where empno=7369            4 2hpm4yjuut7cg    update emp_bak set ename='矮丑穷' where empno=7369
 
2020/5/19 1        192 201c4xcdsjaj0  update emp_bak set ename='沙雕' where empno=7369          221 36xb1pyv12k56    update emp_bak set ename='大长腿' where empno=7369

 

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值