PT - Using Session Waiting Events with Hung or Very Slow Sessions

•  Using Session Waiting Events with Hung or Very Slow Sessions

Scenario Preview: 

We will update and check wait events of sessions

SQL> conn sys/system123@pdb as sysdba

Connected.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

C:\Users\varun>hostname

DESKTOP-H1NR6BO

C:\Users\varun>sqlplus sys/system123@pdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 6 19:39:09 2021

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn soe/soe@pdb

Connected.

• Execute update command on the table salary for scenario.

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

1 row updated.

SQL> SELECT SID, EVENT

  2  FROM V$SESSION

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

       SID EVENT

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

       382 log file sync

• Wait event information of the hung session from the V$SESSION and V$SESSION_EVENT. 

SQL> SELECT SID, EVENT

  2  FROM V$SESSION

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

       SID EVENT

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

         8 enq: TX - row lock contention

       138 log file sync

       260 log file sync

       382 log file sync

       871 log file sync

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: 8

USERNAME: SOE

STATE: WAITING

EVENT: enq: TX - row lock contention

WAIT_TIME: 0

SECONDS_IN_WAIT: 87

WAIT_CLASS: Application

P1TEXT: name|mode

P1: 1415053318

P2TEXT: usn<<16 | slot

P2: 524313

SESSION_WAITS

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

P3TEXT: sequence

P3: 9186

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 - index contention                0

enq: TX - index contention                0

enq: TX - index contention                0

enq: TX - index contention                0

enq: TX - row lock contention           468

SQL> SELECT COUNT(*) FROM V$SESSION_WAIT_HISTORY

  2  WHERE EVENT LIKE 'enq: TX%';

  COUNT(*)

----------

         0

• Rollback or commit statement.

SQL> rollback;

Rollback complete.

• Using v$session now there is no currently waiting events.

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

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

         8 SQL*Net message from client              Idle

       138 SQL*Net message from client              Idle

       260 SQL*Net message from client              Idle

       382 log file sync                            Commit

       505 SQL*Net message to client                Network

       871 SQL*Net message from client              Idle

6 rows selected.

• View reports the wait event that has been waited by the session using V$SESSION_EVENT

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 - index contention                          0

enq: TX - index contention                          0

enq: TX - index contention                          0

enq: TX - index contention                          0

enq: TX - row lock contention                     497

SQL>

• Information about the wait event that has just expired from the V$SESSION_WAIT_HISTORY

SQL> col SESSION_WAITS format a100

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

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

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

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

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

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

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

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

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

 10  FROM V$SESSION_WAIT_HISTORY

 11  WHERE EVENT LIKE 'enq: TX%'

 12  ORDER BY WAIT_TIME;

SESSION_WAITS

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

SID: 8

EVENT: enq: TX - row lock contention

WAIT_TIME: 49718

P1TEXT: name|mode

P1: 1415053318

P2TEXT: usn<<16 | slot

P2: 524313

P3TEXT: sequence

P3: 9186

SQL>

SELECT COUNT(*) FROM V$SESSION_WAIT_HISTORY WHERE EVENT LIKE 'enq: TX%';