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_racUSER_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 DEPTBlocking ->     ‘20,1545‘       devdb1             20 sqlplus@Linux-01 (TNS V1-V3)   oracle  Linux-01        Transaction     Exclusive          666 EMPWaiting         ‘49,1007‘       devdb1             49 sqlplus@Linux-01 (TNS V1-V3)   oracle  Linux-01        Transaction     None               618 EMPWaiting         ‘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_rac2BLOCKING_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 180col user_status format a15col sid_serial format  a15col program format a30 wrappedcol machine format a15 wrappedcol osuser format a15 wrappedcol conn_instance format a15col 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 特性 

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

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

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

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

RAC环境下的阻塞(blocking blocked)