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