Troubleshooting Blocking Session

1. To check what sessions are currently accessing the highly used table HU_TABLE owned by USER1 on ALL NODES

SET LINESIZE 200

SET PAGES 999

COL OBJECT FOR A30

COL PROGRAM FOR A10

COL MACHINE FOR A20

COL LOGON_TIME FOR A20

COL USERNAME FOR A10

COL OSUSER FOR A20

SELECT A.OBJECT,A.TYPE,A.SID,TO_CHAR(B.LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') "LOGON_TIME",B.USERNAME,B.OSUSER,B.PROGRAM,B.MACHINE

FROM V$ACCESS A

JOIN V$SESSION B ON (A.SID = B.SID)

AND A.OBJECT='HU_TABLE'

AND A.TYPE='TABLE';

2. To check if there are any blocking sessions on HU_TABLE on ALL NODES

SET LINESIZE 200

SET PAGES 999

COL OBJECT FOR A30

COL PROGRAM FOR A10

COL MACHINE FOR A20

COL LOGON_TIME FOR A20

COL USERNAME FOR A10

COL OSUSER FOR A20

SELECT S.SID,SQL_HASH_VALUE,S.USERNAME,S.MACHINE,S.EVENT,TO_CHAR(S.LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') "LOGON_TIME",S.MODULE,BLOCKING_INSTANCE "B_INST",BLOCKING_SESSION "B_SID" FROM V$ACCESS A, V$SESSION S WHERE A.SID=S.SID AND S.STATUS='ACTIVE' AND OBJECT='HU_TABLE' AND OWNER='USER1' AND BLOCKING_SESSION IS NOT NULL AND USERNAME IS NOT NULL;

3. To Check Blocking Sessions on ALL NODES

SET LINESIZE 200 PAGES 999

COL SID FOR 9999

COL USERNAME FOR A8

COL MACHINE FOR A23

COL EVENT FOR A50

COL MODULE FOR A40

COL B_SID FOR 9999

COL B_INST FOR 9

COL LOGON_TIME FOR A20

SELECT SID,SQL_HASH_VALUE,USERNAME,MACHINE,EVENT,TO_CHAR(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') "LOGON_TIME",MODULE,BLOCKING_INSTANCE "B_INST",BLOCKING_SESSION "B_SID" FROM V$SESSION WHERE STATUS='ACTIVE' AND BLOCKING_SESSION IS NOT NULL AND USERNAME IS NOT NULL;

4. If any of the above query (2 and 3) shows blocking sessions then we need to find out the detials of the blocking sessions

Please not that B_Inst depcits which node is blocking. Example: if the value of B_Inst = 3 then please run the below query on node3

SET LINESIZE 200 PAGES 999

COL SID FOR 9999

COL USERNAME FOR A8

COL MACHINE FOR A23

COL EVENT FOR A50

COL MODULE FOR A40

COL B_SID FOR 9999

COL B_INST FOR 9

COL LOGON_TIME FOR A20

SELECT SID,SERIAL#,SQL_HASH_VALUE,USERNAME,MACHINE,EVENT,TO_CHAR(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') "LOGON_TIME",MODULE,BLOCKING_INSTANCE "B_INST",BLOCKING_SESSION "B_SID" FROM V$SESSION WHERE SID='&SID';

5. Fetch the SQL of session for the SID returned (Enter the SQL_HASH_VALUE)

SET LONG 9999

SELECT A.SID,B.SQL_TEXT FROM V$SESSION A, V$SQLTEXT B WHERE A.SQL_HASH_VALUE = B.HASH_VALUE AND A.SQL_HASH_VALUE='';

6. Kill sessions as per instructed by Application Team ( Sid and Serial# from Query no. 4)

ALTER SYSTEM KILL SESSION '&SID','&serial#' IMMEDIATE;

7. Verify Killed Sessions ( How much time they will take once they have been marked killed)

SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK FROM V$SESSION A, V$TRANSACTION B WHERE A.SADDR = B.SES_ADDR AND A.STATUS ='KILLED';