Use Case: Using Session Waiting Events with Hung or Very Slow Sessions

Use Case: Using Session Waiting Events with Hung or Very Slow Sessions 

Scenario Overview:

we have 2 client session 1 &2 . We will update same row on table in both session and do not commit after session 1execution . and try to update same query on session 2 .

Checking the sessions details of waiter and blocker of session.

 SESSION 1

C:\Users\varunyadav>sqlplus soe/soe@orcl2

SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 24 16:30:41 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Tue Nov 24 2020 16:30:13 +05:30

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>  UPDATE EMP SET SALARY=SALARY WHERE EMP_NO=104;

1 row updated.

SQL>

 SESSION 2

C:\Users\varunyadav>sqlplus soe/soe@orcl2

SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 24 16:30:41 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Tue Nov 24 2020 16:30:13 +05:30

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>  UPDATE EMP SET SALARY=SALARY WHERE EMP_NO=104;

( Session above stuck as session 1 is not commit there update)

• Checking the idle or hung  session details using query

C:\Users\varunyadav>sqlplus soe/soe@orcl2

SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 24 16:35:10 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Tue Nov 24 2020 16:30:41 +05:30

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> SELECT SID, EVENT

  2  FROM V$SESSION

  3  WHERE STATE='WAITING' AND USERNAME ='SOE' AND WAIT_CLASS<>'Idle';

       SID EVENT

---------- ----------------------------------------------------------------

       126 enq: TX - row lock contention

• Hung session information 

SQL> col SESSION_WAITS format a100

SQL> SELECT 'SID: '|| SID||

  2  CHR(10)||'USERNAME: '|| USERNAME||

  3  CHR(10)||'STATE: '|| STATE||

  4  CHR(10)||'EVENT: '|| EVENT||

  5  CHR(10)||'WAIT_TIME: '|| WAIT_TIME||

  6  CHR(10)||'SECONDS_IN_WAIT: '|| SECONDS_IN_WAIT||

  7  CHR(10)||'WAIT_CLASS: '|| WAIT_CLASS||

  8  CHR(10)||'P1TEXT: '|| P1TEXT||

  9  CHR(10)||'P1: '|| P1||

 10  CHR(10)||'P2TEXT: '|| P2TEXT||

 11  CHR(10)||'P2: '|| P2 ||

 12  CHR(10)||'P3TEXT: '|| P3TEXT||

 13  CHR(10)||'P3: ' || P3 AS SESSION_WAITS

 14  FROM V$SESSION

 15  WHERE USERNAME='SOE' AND EVENT LIKE 'enq: TX%'

 16  ORDER BY WAIT_TIME;

SESSION_WAITS

--------------------------------------------------------------------------------

SID: 126

USERNAME: SOE

STATE: WAITING

EVENT: enq: TX - row lock contention

WAIT_TIME: 0

SECONDS_IN_WAIT: 280

WAIT_CLASS: Application

P1TEXT: name|mode

P1: 1415053318

P2TEXT: usn<<16 | slot

P2: 524304

SESSION_WAITS

--------------------------------------------------------------------------------

P3TEXT: sequence

P3: 1381

SQL> col EVENT for a30

SQL> SELECT E.EVENT,

  2  TO_CHAR(ROUND(E.TIME_WAITED/100),'999,999,999') TIME_SECONDS

  3  FROM V$SESSION_EVENT E, V$SESSION S

  4  WHERE E.SID=S.SID AND S.USERNAME='SOE' AND E.EVENT LIKE 'enq: TX%'

  5  ORDER BY TIME_WAITED;

EVENT                          TIME_SECONDS

------------------------------ ------------

enq: TX - row lock contention           307

• Retrieve session history details

SQL> SELECT COUNT(*) FROM V$SESSION_WAIT_HISTORY

  2  WHERE EVENT LIKE 'enq: TX%';

  COUNT(*)

----------

         0

SQL>

<16 --------------------------------------------------------------------------------="" 1381="" 524304="" p2:="" p3:="" p3text:="" sequence="" session_waits="" slot="" sql="">ROLLBACK

<16 --------------------------------------------------------------------------------="" 1381="" 524304="" p2:="" p3:="" p3text:="" sequence="" session_waits="" slot="" sql="">Session 1

SQL> UPDATE EMP SET SALARY=SALARY WHERE EMP_NO=104; 

 1 row updated. 

 SQL> rollback; 

 Rollback complete. SQL>

<16 --------------------------------------------------------------------------------="" 1381="" 524304="" p2:="" p3:="" p3text:="" sequence="" session_waits="" slot="" sql="">Session 2

SQL> UPDATE EMP SET SALARY=SALARY WHERE EMP_NO=104; 

 1 row updated. 

 SQL> rollback; 

 Rollback complete. 

• Wait event of sessions of schema

SQL>

SQL> col EVENT format a40

SQL> col WAIT_CLASS format a10

SQL> SELECT SID, EVENT, WAIT_CLASS

  2  FROM V$SESSION

  3  WHERE USERNAME='SOE';

       SID EVENT                                    WAIT_CLASS

---------- ---------------------------------------- ----------

        10 SQL*Net message to client                Network

       126 SQL*Net message from client              Idle

       867 SQL*Net message from client              Idle

SQL>