Blockingblocked in RAC Environment)

The blocking in the RAC environment is different from that in a single instance, because we need to consider the session in different instances. That is to say, the corresponding strain of the previously queried v $ session and v $ lock is global. This article provides two query scripts, and provides an example to demonstrate which sessions are blocked and which are blocked. Concept of blocking and single-instance Ring

The blocking in the RAC environment is different from that in a single instance, because we need to consider the session in different instances. That is to say, the corresponding strain of the previously queried v $ session and v $ lock is global. This article provides two query scripts, and provides an example to demonstrate which sessions are blocked and which are blocked. Concept of blocking and single-instance Ring

The blocking in the RAC environment is different from that in a single instance, because we need to consider the session in different instances. That is to say, the corresponding strain of the previously queried v $ session and v $ lock is global. This article provides two query scripts, and provides an example to demonstrate which sessions are blocked and which are blocked. For the concept of blocking and blocking in a single instance environment, see Oracle blocking (blocking blocked)

1. Demo Environment

Scott @ DEVDB> select * from v $ version where rownum <2; BANNER implements Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production -- publish SQL statements in scott session, 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 leshami @ DEVDB> update scott in the leshami session. emp set sal = sal-200 where empno = 7788; -- update emp object usr1 @ DEVDB> update scott in usr1 session. dept set dname = 'dev' where deptno = 10;

2. Search for blocking

Scott @ DEVDB> @ inclusid_serial CONN_INSTANCE sid program osuser machine LOCK_TYPE LOCK_MODE CTIME OBJECT_NAME too far ------------------ ---- upper ------- upper -------------- lower Blocking-> '100 5' devdb1 20 sqlplus @ Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusi Ve 666 DEPTBlocking-> '100 5' devdb1 20 sqlplus @ Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 20,154 EMPWaiting '100 7' devdb1 49 sqlplus @ Linux-01 (TNS V1-V3) oracle Linux-01 Transaction None 618 EMPWaiting '933,116 91 'devdb2 933 sqlplus @ Linux-02 (TNS V1-V3) oracle Linux-02 Transaction None 558 DEPT -- through the above script we can see that session '123456' locks the object DEPT and EMP, and at this time the session '12347' and '1234569' are in the waiting state. -- The following is another way to get the blocking situation scott @ DEVDB> @ block_session_rac2BLOCKING_STATUS into 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

[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; 

More references

DML Error Logging

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % FOUND)

Batch SQL FORALL statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution PLAN