Introduction and demonstration of blocking (blocking blocked) in Oracle RAC Environment _oracle

Blocking in a RAC environment is different from a single-instance case, because we need to take into account the sessions that are in different instances. That is to say, the v$session,v$lock corresponding to the previous query to the global scope to find. This article provides 2 query scripts and shows examples of which sessions are blocked and which are blocked. For the concept of blocking and blocking in a single-instance environment please refer to: Oracle blocking (blocking blocked)

1. Demo Environment

Scott@devdb> SELECT * from v$version where rownum<2;

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

--Publish SQL statements in Scott session, not submitted
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.

--Update the EMP object in Leshami session
leshami@devdb> update scott.emp set sal=sal-200 where empno=7788;

--Update the EMP object in USR1 session
usr1@devdb> update scott.dept set dname= ' DEV ' where deptno=10;

2. Looking for obstruction

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 sqlplus@linux-01 (TNS v1-v3) Oracle Linux-01 Transaction Exclusive 666 DEPT Blocking ' 20,1545 ' devdb1 sqlplus@linux-01 (TNS v1-v3) Oracle Linux-01 Transaction Exclusive EMP 666 G ' 49,1007 ' devdb1 sqlplus@linux-01 (TNS v1-v3) Oracle Linux-01 Transaction None 618 EMP wait

ing ' 933,11691 ' devdb2 933 sqlplus@linux-02 (TNS v1-v3) Oracle Linux-02 Transaction None 558 DEPT

Through the script we can see the session ' 20,1545 ' Lock the object dept and the EMP, while session ' 49,1007 ' and ' 933,11691 ' are in the waiting state.

--Here's another way to get a blocking case scott@devdb> @block_session_rac2Blocking_status----------------------------------------------------------------------------------------------- -----------------------------scott@linux-01 (inst=1 sid=20 serail#=1545) is BLOCKING usr1@linux-02 (inst=2 sid=933 Ser ial#=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, script used in the demo

[oracle@linux-01 ~]$ more Block_session_rac.sql set linesize 180 col user_status format A15 col sid_serial format A15 Co  L 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 (sid_serial), (SELECT instance_name from gv$instance WHERE inst_id = l.inst_id) Conn_instanc
         E, 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 . blocks, 0, ' not Blocking ', 1, ' Blocking ', 2, ' Global '] lock_status, object_name from Gv$lock l JOIN gv$se Ssion 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.Si D = o.session_id) JOIN dba_objects D on (d.object_id = o.object_id) WHERE (L.id1, L.id2, L.type) in (SELECT id1, ID

2, 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.si D 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;