Shulou(Shulou.com)06/01 Report--

Blocking in the RAC environment is different from the single-instance case because we need to take into account the session in different instances. That is to say, the previously queried vaccounsessionprecinct vroomlock should be changed to the global scope to find it. This article provides two query scripts and gives examples to demonstrate which session is blocked and which is blocked. For the concept of blocking and blocking in a single instance environment, please refer to: Oracle blocking (blocking blocked)

1. Demo environment

[sql] view plain copy print?scott@DEVDB > select * from v$version where rownum 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 emp objects in leshami session leshami@DEVDB > update scott.emp set sal=sal-200 where empno=7788;-- update emp objects usr1@DEVDB > update scott.dept set dname='DEV' where deptno=10 in usr1 session

2. Look for blockages

[sql] view plain copy print?scott@DEVDB > @ block_session_racUSER_STATUS SID_SERIAL CONN_INSTANCE SID PROGRAM OSUSER MACHINE LOCK_TYPE LOCK_MODE CTIME OBJECT_NAME-- -Blocking->'20 sqlplus@Linux-01 1545 'devdb1 20 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 666 DEPTBlocking- > '20sqlplus@Linux-02 1545' devdb1 20 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 666 EMPWaiting'49 devdb1 49 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction None 618 EMPWaiting '933Magna 11691' devdb2 933 sqlplus@Linux-02 (TNS V1-V3) oracle Linux-02 Transaction None 558 DEPT-- through the above script we can see that session '20Magne1545' locks the object DEPT and EMP At this time, session '49jue 1007' and' 93jue 11691 'are in a waiting state. Here is another way to get the blocking situation 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. Scripts used in the demo

[sql] view plain copy print? [oracle@Linux-01 ~] $more block_session_rac.sqlset 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 wrappedSELECT DECODE (l.block, 0, 'Waiting',' Blocking->') user_status,CHR (39) | | s.sid | |','| s.serial# | | CHR (39) sid_serial, (SELECT instance_nameFROM gv$instanceWHERE inst_id = l.inst_id) conn_instance S. Siddre.programmer. Osuserpr. MachineDeCODE (l.TYPEL 'Dictionary','TM',' DML','TS', 'Temp Segments','TX',' Transaction','UL', 'User','RW',' Row Wait',l.TYPE) lock_type--,id1--, id2, DECODE (l.lmodePower0, '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_nameFROM gv$lock lJOIN gv$session s ON (l.inst_id = s.inst_id AND l.sid = s.sid) JOIN gv$locked_object oON (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, TYPEFROM gv$lockWHERE request > 0) ORDER BY id1, id2, ctime DESC [oracle@Linux-01 ~] $more block_session_rac2.sqlSELECT DISTINCTs1.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_statusFROM gv$lock L1 gvcentury session s1 method gvicilock L2 Gv$session s2WHERE s1.sid = l1.sidAND s2.sid = l2.sidAND s1.inst_id = l1.inst_idAND s2.inst_id = l2.inst_idAND l1.block > 0AND l2.request > 0AND l1.id1 = l2.id1AND l1.id2 = l2.id2

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.