PL/SQL

How to Identify Blocking Sessions

Here I introduce a sample of PL/SQL code to monitor the blocking sessions every N minutes and output in a formatted report.

First of all, edit the sql script.
[oracle@primary01 ~]$ vi CheckBlockingSessions.sql
SET SERVEROUTPUT ON;
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
DECLARE
  V_MAX_COUNT NUMBER := 4;
  V_WAIT_MIN  NUMBER := 5;
  CURSOR C_BLOCKING
  IS
    SELECT BLOCKER.INST_ID,
      BLOCKER.SID BLOCKER_SID ,
      WAITING.SID WAITING_SID ,
      TRUNC(WAITING.CTIME/60) MIN_WAITING ,
      WAITING.REQUEST
    FROM
      (SELECT * FROM GV$LOCK WHERE BLOCK != 0 AND TYPE = 'TX'
      ) BLOCKER ,
    GV$LOCK WAITING
  WHERE WAITING.TYPE  ='TX'
  AND WAITING.BLOCK   = 0
  AND BLOCKER.INST_ID = WAITING.INST_ID
  AND WAITING.ID1     = BLOCKER.ID1
  ORDER BY 1,2,3;
  V_CUR_REC C_BLOCKING%ROWTYPE;
BEGIN
  FOR I IN 1..V_MAX_COUNT
  LOOP
      DBMS_OUTPUT.PUT_LINE('======== Begin To Check Blocking Sessions '||SYSDATE||' ========');
      OPEN C_BLOCKING;
      DBMS_OUTPUT.PUT_LINE(LPAD('INST_ID',8)||LPAD('BLOCKER_SID',12)||LPAD('WAITING_SID',12)||LPAD('MIN_WAITING',12)||LPAD('REQUEST',8));
      DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-')||RPAD(' ',12,'-')||RPAD(' ',12,'-')||RPAD(' ',12,'-')||RPAD(' ',8,'-'));
      LOOP
        FETCH C_BLOCKING INTO V_CUR_REC;
        EXIT
      WHEN C_BLOCKING%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(LPAD(V_CUR_REC.INST_ID,8)||LPAD(V_CUR_REC.BLOCKER_SID,12)||LPAD(V_CUR_REC.WAITING_SID,12)||LPAD(V_CUR_REC.MIN_WAITING,12)||LPAD(V_CUR_REC.REQUEST,8));
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-')||RPAD(' ',12,'-')||RPAD(' ',12,'-')||RPAD(' ',12,'-')||RPAD(' ',8,'-'));
      IF C_BLOCKING%ROWCOUNT = 0 THEN
        DBMS_OUTPUT.PUT_LINE('-> NO ROWS SELECTED');
      ELSE
        DBMS_OUTPUT.PUT_LINE('FOUND '||C_BLOCKING%ROWCOUNT||' RECORDS');
      END IF;
      CLOSE C_BLOCKING;
      DBMS_OUTPUT.PUT_LINE('========   End To Check Blocking Sessions '||SYSDATE||' ========');
      IF I < V_MAX_COUNT THEN
        DBMS_OUTPUT.PUT_LINE('WAITING FOR '||V_WAIT_MIN||' MINS.');
        DBMS_LOCK.SLEEP(V_WAIT_MIN * 60);
        DBMS_OUTPUT.PUT_LINE(CHR(10));
      END IF;
  END LOOP;
END;
/

In the above code, the key statement is the cursor definition that can monitor the blocking sessions presently:
    SELECT BLOCKER.INST_ID,
      BLOCKER.SID BLOCKER_SID ,
      WAITING.SID WAITING_SID ,
      TRUNC(WAITING.CTIME/60) MIN_WAITING ,
      WAITING.REQUEST
    FROM
      (SELECT * FROM GV$LOCK WHERE BLOCK != 0 AND TYPE = 'TX'
      ) BLOCKER ,
    GV$LOCK WAITING
  WHERE WAITING.TYPE  ='TX'
  AND WAITING.BLOCK   = 0
  AND BLOCKER.INST_ID = WAITING.INST_ID
  AND WAITING.ID1     = BLOCKER.ID1
  ORDER BY 1,2,3;

After finishing the sql scirpt, we can execute the block of code.
[oracle@primary01 ~]$ sqlplus / as sysdba @CheckBlockingSessions.sql
...
======== Begin To Check Blocking Sessions 2013-03-05 16:31:39 ========
INST_ID BLOCKER_SID WAITING_SID MIN_WAITING REQUEST
------- ----------- ----------- ----------- -------
1         111         110         430       6
1         111         123         429       6
------- ----------- ----------- ----------- -------
FOUND 2 RECORDS
========   End To Check Blocking Sessions 2013-03-05 16:31:39 ========
WAITING FOR 5 MINS.


======== Begin To Check Blocking Sessions 2013-03-05 16:36:39 ========
INST_ID BLOCKER_SID WAITING_SID MIN_WAITING REQUEST
------- ----------- ----------- ----------- -------
1         111         110         435       6
1         111         123         434       6
------- ----------- ----------- ----------- -------
FOUND 2 RECORDS
========   End To Check Blocking Sessions 2013-03-05 16:36:39 ========
WAITING FOR 5 MINS.


======== Begin To Check Blocking Sessions 2013-03-05 16:41:39 ========
INST_ID BLOCKER_SID WAITING_SID MIN_WAITING REQUEST
------- ----------- ----------- ----------- -------
1         111         110         440       6
1         111         123         439       6
------- ----------- ----------- ----------- -------
FOUND 2 RECORDS
========   End To Check Blocking Sessions 2013-03-05 16:41:39 ========
WAITING FOR 5 MINS.


======== Begin To Check Blocking Sessions 2013-03-05 16:46:39 ========
INST_ID BLOCKER_SID WAITING_SID MIN_WAITING REQUEST
------- ----------- ----------- ----------- -------
1         111         110         445       6
1         111         123         444       6
------- ----------- ----------- ----------- -------
FOUND 2 RECORDS
========   End To Check Blocking Sessions 2013-03-05 16:46:39 ========

PL/SQL procedure successfully completed.

SQL>

If you like to use the code on your job, you can change the default value of variables for your need.

The above anonymous PL/SQL is quietly simple, however, there is a defect that the output buffer will not flush until the end of execution. You can try to output the data by UTL_FILE or UTL_SMTP instead of DBMS_OUTPUT.

Leave a Reply

Your email address will not be published. Required fields are marked *