RAC环境下的阻塞(blocking blocked)

      RAC环境下的阻塞不同于单实例情形,因为我们需要考虑到位于不同实例的session。也就是说之前查询的v$session,v$lock相应的应变化为全局范围来查找。本文提供了2个查询脚本,并给出实例演示那些session为阻塞者,哪些为被阻塞者。有关阻塞的概念以及单实例环境下的阻塞请参考:Oracle 阻塞(blocking blocked)

1、演示环境

scott@DEVDB> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--在scott session中发布SQL语句,并未提交
scott@DEVDB> begin
  2  update emp set sal=sal+100 where empno=7788;
  3  update dept set dname='DBA' where deptno=10;
  4  end;
  5  /

PL/SQL procedure successfully completed.

--在leshami session中更新emp对象
leshami@DEVDB> update scott.emp set sal=sal-200 where empno=7788;

--在usr1 session中更新emp对象
usr1@DEVDB> update scott.dept set dname='DEV' where deptno=10;

2、寻找阻塞

scott@DEVDB> @block_session_rac

USER_STATUS     SID_SERIAL      CONN_INSTANCE     SID PROGRAM                        OSUSER  MACHINE         LOCK_TYPE       LOCK_MODE        CTIME OBJECT_NAME
--------------- --------------- ---------------- ---- ------------------------------ ------- --------------- --------------- ----------- ---------- -------------------------
Blocking ->     '20,1545'       devdb1             20 sqlplus@Linux-01 (TNS V1-V3)   oracle  Linux-01        Transaction     Exclusive          666 DEPT
Blocking ->     '20,1545'       devdb1             20 sqlplus@Linux-01 (TNS V1-V3)   oracle  Linux-01        Transaction     Exclusive          666 EMP
Waiting         '49,1007'       devdb1             49 sqlplus@Linux-01 (TNS V1-V3)   oracle  Linux-01        Transaction     None               618 EMP
Waiting         '933,11691'     devdb2            933 sqlplus@Linux-02 (TNS V1-V3)   oracle  Linux-02        Transaction     None               558 DEPT

--通过上述脚本我们可以看到session '20,1545' 锁住了对象DEPT以及EMP,而此时session '49,1007'与'933,11691'处于等待状态。

--下面是另外的一种方式来获取阻塞的情形
scott@DEVDB> @block_session_rac2

BLOCKING_STATUS
----------------------------------------------------------------------------------------------------------------------------
SCOTT@Linux-01 ( INST=1 SID=20 Serail#=1545 ) IS BLOCKING USR1@Linux-02 ( INST=2 SID=933 Serial#=11691 )
SCOTT@Linux-01 ( INST=1 SID=20 Serail#=1545 ) IS BLOCKING LESHAMI@Linux-01 ( INST=1 SID=49 Serial#=1007 )

--Author : Leshami
--Blog   : http://blog.csdn.net/leshami

3、演示中用到的脚本

[oracle@Linux-01 ~]$ more block_session_rac.sql 
set linesize 180
col user_status format a15
col sid_serial format  a15
col program format a30 wrapped
col machine format a15 wrapped
col osuser format a15 wrapped
col conn_instance format a15
col object_name format a25 wrapped
 SELECT DECODE (l.block, 0, 'Waiting', 'Blocking ->') user_status,
         CHR (39) || s.sid || ',' || s.serial# || CHR (39) sid_serial,
         (SELECT instance_name
            FROM gv$instance
           WHERE inst_id = l.inst_id)
            conn_instance,
         s.sid,
         s.program,
         s.osuser,
         s.machine,
         DECODE (l.TYPE,
                 'RT', 'Redo Log Buffer',
                 'TD', 'Dictionary',
                 'TM', 'DML',
                 'TS', 'Temp Segments',
                 'TX', 'Transaction',
                 'UL', 'User',
                 'RW', 'Row Wait',
                 l.TYPE)
            lock_type--,id1
                     --,id2
         ,
         DECODE (l.lmode,
                 0, 'None',
                 1, 'Null',
                 2, 'Row Share',
                 3, 'Row Excl.',
                 4, 'Share',
                 5, 'S/Row Excl.',
                 6, 'Exclusive',
                 LTRIM (TO_CHAR (lmode, '990')))
            lock_mode,
         ctime--,DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') lock_status
         ,
         object_name
    FROM gv$lock l
         JOIN gv$session s ON (l.inst_id = s.inst_id AND l.sid = s.sid)
         JOIN gv$locked_object o
            ON (o.inst_id = s.inst_id AND s.sid = o.session_id)
         JOIN dba_objects d ON (d.object_id = o.object_id)
   WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE
                                      FROM gv$lock
                                     WHERE request > 0)
ORDER BY id1, id2, ctime DESC;

[oracle@Linux-01 ~]$ more block_session_rac2.sql 
SELECT DISTINCT
          s1.username
       || '@'
       || s1.machine
       || ' ( INST='
       || s1.inst_id
       || ' SID='
       || s1.sid
       || ' Serail#='
       || s1.serial#
       || ' ) IS BLOCKING '
       || s2.username
       || '@'
       || s2.machine
       || ' ( INST='
       || s2.inst_id
       || ' SID='
       || s2.sid
       || ' Serial#='
       || s2.serial#
       || ' ) '
          AS blocking_status
  FROM gv$lock l1,
       gv$session s1,
       gv$lock l2,
       gv$session s2
 WHERE     s1.sid = l1.sid
       AND s2.sid = l2.sid
       AND s1.inst_id = l1.inst_id
       AND s2.inst_id = l2.inst_id
       AND l1.block > 0
       AND l2.request > 0
       AND l1.id1 = l2.id1
       AND l1.id2 = l2.id2;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

一个SQL性能问题的优化探索(二)(r11笔记第38天)

继续前几天的一个案例一个SQL性能问题的优化探索(一)(r11笔记第33天) 如下的SQL语句存在索引字段CARD_NO,但是执行的时候却走了全表扫描,因为这是...

3568
来自专栏乐沙弥的世界

数据导入时遭遇 ORA-01187 ORA-01110

最近的数据导入(IMP)时碰到了ORA-01187 ORA-01110 错误,由于这个数据库是使用热备恢复过来的,且恢复也是成功的,因为数据库能够成功open,...

843
来自专栏杨建荣的学习笔记

关于dual表的破坏性测试(r3笔记第60天)

关于dual表的破坏性测试,既然是破坏性测试,就需要确定这个测试仅限于测试或者个人学习所用,可能有些sql看似极为简单,但是一旦运行就会导致整个业务系统崩溃。 ...

39813
来自专栏杨建荣的学习笔记

关于两个简单问题的分析(r9笔记第10天)

工作中碰到问题当然是见怪不怪了,而处理这些问题也是我们的价值所在。 今天处理了几个看起来比较有意思的小问题,当然究其原因,要不是不规范,要不就是基本功不够扎实。...

3024
来自专栏杨建荣的学习笔记

关于consistent gets(r5笔记第12天)

在sql调优的时候,一个关键指标就是consistent gets,如果这个指标很低,一般认为sql语句执行还是很高效的,反之效率会很低。但是这个指标我们知之甚...

3375
来自专栏乐沙弥的世界

Oracle 控制文件(CONTROLFILE)

为二进制文件,初始化大小由CREATE DATABASE指定,可以使用RMAN备份

1162
来自专栏杨建荣的学习笔记

merge语句导致的CPU使用率过高的优化(r7笔记第4天)

今天有一个数据库有点反常,早上的时候报出了CPU使用率的警告。 警告内容如下: ZABBIX-监控系统: -----------------------...

3285
来自专栏杨建荣的学习笔记

创建index 使用Online导致的问题(95天)

在本地的测试库中,本来空间就不足,结果创建了一个表有600多万条记录,想创建一个index. 物理段有340多M. 临时段大小有100M,结果想创建一个索引,总...

36813
来自专栏沃趣科技

应用示例荟萃 | performance_schema全方位介绍(上)

经过前面6个篇幅的学习,相信大家对什么是performance_schema,已经初步形成了一个整体认识,但我想很多同行看完之前的文章之后可能还是一脸懵逼,今天...

2183
来自专栏杨建荣的学习笔记

关于oracle invalid components问题的解决(28天)

升级生产的DB,有一些预检查条件,这些需要提前修复,以便在升级的时候不会成为影响进度的绊脚石。 生产中的components有一些显示是invalid状态,从d...

3784

扫码关注云+社区

领取腾讯云代金券