RAC环境下的阻塞不同于单实例情形,因为我们需要考虑到位于不同实例的session。也就是说之前查询的v$session,v$lock相应的应变化为全局范围来查找。本文提供了2个查询脚本,并给出实例演示那些session为阻塞者,哪些为被阻塞者。有关阻塞的概念以及单实例环
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=&#39;DBA&#39; 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=&#39;DEV&#39; 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 -> &#39;20,1545&#39; devdb1 20 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 666 DEPT Blocking -> &#39;20,1545&#39; devdb1 20 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 666 EMP Waiting &#39;49,1007&#39; devdb1 49 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction None 618 EMP Waiting &#39;933,11691&#39; devdb2 933 sqlplus@Linux-02 (TNS V1-V3) oracle Linux-02 Transaction None 558 DEPT --通过上述脚本我们可以看到session &#39;20,1545&#39; 锁住了对象DEPT以及EMP,而此时session &#39;49,1007&#39;与&#39;933,11691&#39;处于等待状态。 --下面是另外的一种方式来获取阻塞的情形 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, &#39;Waiting&#39;, &#39;Blocking ->&#39;) user_status, CHR (39) || s.sid || &#39;,&#39; || 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, &#39;RT&#39;, &#39;Redo Log Buffer&#39;, &#39;TD&#39;, &#39;Dictionary&#39;, &#39;TM&#39;, &#39;DML&#39;, &#39;TS&#39;, &#39;Temp Segments&#39;, &#39;TX&#39;, &#39;Transaction&#39;, &#39;UL&#39;, &#39;User&#39;, &#39;RW&#39;, &#39;Row Wait&#39;, l.TYPE) lock_type--,id1 --,id2 , DECODE (l.lmode, 0, &#39;None&#39;, 1, &#39;Null&#39;, 2, &#39;Row Share&#39;, 3, &#39;Row Excl.&#39;, 4, &#39;Share&#39;, 5, &#39;S/Row Excl.&#39;, 6, &#39;Exclusive&#39;, LTRIM (TO_CHAR (lmode, &#39;990&#39;))) lock_mode, ctime--,DECODE(l.BLOCK, 0, &#39;Not Blocking&#39;, 1, &#39;Blocking&#39;, 2, &#39;Global&#39;) 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 || &#39;@&#39; || s1.machine || &#39; ( INST=&#39; || s1.inst_id || &#39; SID=&#39; || s1.sid || &#39; Serail#=&#39; || s1.serial# || &#39; ) IS BLOCKING &#39; || s2.username || &#39;@&#39; || s2.machine || &#39; ( INST=&#39; || s2.inst_id || &#39; SID=&#39; || s2.sid || &#39; Serial#=&#39; || s2.serial# || &#39; ) &#39; 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 联合数组与嵌套表
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
使用 EXPLAIN PLAN 获取SQL语句执行计划