Wednesday, March 15, 2017

How to find wait information about a session


Yesterday, I had started a script with two long-running queries before I my shift ended, and it was finished when I got back, as expected. The session was still connected to the database and waiting for some time. I was curious how long time the SQL took, and I forgot to set timing on in sqlplus.

SELECT 
    S.SID,
    S.SERIAL#,
    S.STATUS "status",
    S.LOGON_TIME "logon time",
    S.PREV_EXEC_START "last op started",
    Q.LAST_ACTIVE_TIME "last op ended", 
    S.STATE "state",
    S.EVENT "event",
    ROUND((S.WAIT_TIME_MICRO/1000000)/60/60,1) "total wait hrs",
    DECODE(S.TIME_REMAINING_MICRO,'-1', 'indefinite', '0','Wait timed out',NULL,'session not waiting') "remaining wait time", 
    DECODE(S.TIME_SINCE_LAST_WAIT_MICRO, '0','Still waiting') "current status"
FROM V$SESSION S  JOIN V$SQL Q 
ON (S.SQL_ID = Q.SQL_ID)
WHERE SID=1758;

The output below shows that my session was established at 14:33, and the last operation ended at 19:20. In other words, it took approximately 5 hours for the two statements in my script to finish:

SID SERIAL# status logon time last op started last op ended state event total wait hrs remaining wait time current status
1758
35360
INACTIVE 14.03.2017 14:33:42 14.03.2017 19:10:31 14.03.2017 19:20:52 WAITING SQL*Net message from client
13,2
indefinite Still waiting


Note that v$session column "seconds_in_wait" has been deprecated. You should use "wait_time_micro" instead.

No comments:

Post a Comment