RAC环境下的阻塞(blocking blocked)

我不是女神ヾ 2022-08-23 11:58 193阅读 0赞

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

更多参考

[DML Error Logging 特性 ][DML_Error Logging]

[PL/SQL --> 游标][PL_SQL --_]

[PL/SQL --> 隐式游标(SQL%FOUND)][PL_SQL --_ _SQL_FOUND]

[批量SQL之 FORALL 语句][SQL_ FORALL]

[批量SQL之 BULK COLLECT 子句][SQL_ BULK COLLECT]

[PL/SQL 集合的初始化与赋值  
][PL_SQL _]

[PL/SQL 联合数组与嵌套表][PL_SQL _]  
[PL/SQL 变长数组][PL_SQL]  
[PL/SQL --> PL/SQL记录][PL_SQL --_ PL_SQL]

[SQL tuning 步骤][SQL tuning]

[高效SQL语句必杀技  
][SQL_]

[父游标、子游标及共享游标][Link 1]

[绑定变量及其优缺点][Link 2]

[dbms\_xplan之display\_cursor函数的使用][dbms_xplan_display_cursor]

[dbms\_xplan之display函数的使用][dbms_xplan_display]

[执行计划中各字段各模块描述][Link 3]

[使用 EXPLAIN PLAN 获取SQL语句执行计划][EXPLAIN PLAN _SQL]

[Oracle _blocking blocked]: http://blog.csdn.net/leshami/article/details/37565935
[DML_Error Logging]: http://blog.csdn.net/robinson_0612/article/details/7783724
[PL_SQL --_]: http://blog.csdn.net/robinson_0612/archive/2010/12/15/6078622.aspx
[PL_SQL --_ _SQL_FOUND]: http://blog.csdn.net/robinson_0612/archive/2010/12/22/6092066.aspx
[SQL_ FORALL]: http://blog.csdn.net/robinson_0612/article/details/7536926
[SQL_ BULK COLLECT]: http://blog.csdn.net/robinson_0612/article/details/7545597
[PL_SQL _]: http://blog.csdn.net/robinson_0612/article/details/7372061
[PL_SQL]: http://blog.csdn.net/robinson_0612/article/details/7387647
[PL_SQL --_ PL_SQL]: http://blog.csdn.net/robinson_0612/article/details/6084390
[SQL tuning]: http://blog.csdn.net/robinson_0612/article/details/7422254
[SQL_]: http://blog.csdn.net/robinson_0612/article/details/7406672
[Link 1]: http://blog.csdn.net/robinson_0612/article/details/6893477
[Link 2]: http://blog.csdn.net/robinson_0612/article/details/6904229
[dbms_xplan_display_cursor]: http://blog.csdn.net/robinson_0612/article/details/6866925
[dbms_xplan_display]: http://blog.csdn.net/robinson_0612/article/details/6866870
[Link 3]: http://blog.csdn.net/robinson_0612/article/details/6860007
[EXPLAIN PLAN _SQL]: http://blog.csdn.net/robinson_0612/article/details/6837771