V$SESSION_WAIT SECONDS_IN_WAIT Column

This was tested on Oracle 11.2.0.1.0. I created a user in the red session below and used it to login to the database:

 

SQL> grant create session to andrew

  2  identified by reid

  3  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL>

 

While this session was waiting, I logged in to the green session below and looked in V$SESSION_WAIT to see what it was waiting for:

 

SQL> select b.event, b.seconds_in_wait

  2  from v$session a, v$session_wait b

  3  where a.sid = b.sid

  4  and username = 'ANDREW'

  5  /

 

EVENT                          SECONDS_IN_WAIT

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

SQL*Net message from client                642

 

SQL> exec dbms_lock.sleep(10);

 

PL/SQL procedure successfully completed.

 

SQL> select b.event, b.seconds_in_wait

  2  from v$session a, v$session_wait b

  3  where a.sid = b.sid

  4  and username = 'ANDREW'

 5  /

 

EVENT                          SECONDS_IN_WAIT

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

SQL*Net message from client                652

 

SQL>

 

In the 10 second delay between the first query and the second, the value in the SECONDS_IN_WAIT column went up by 10, showing that the session was actively waiting on this event.

 

In Oracle 10, the information from V$SESSION_WAIT was included in V$SESSION so I could have used the following query instead:

 

SQL> select event, seconds_in_wait

  2  from v$session

  3  where username = 'ANDREW'

  4  /

 

EVENT                          SECONDS_IN_WAIT

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

SQL*Net message from client               2036

 

SQL> exec dbms_lock.sleep(10);

 

PL/SQL procedure successfully completed.

 

SQL> select event, seconds_in_wait

  2  from v$session

  3  where username = 'ANDREW'

  4  /

 

EVENT                          SECONDS_IN_WAIT

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

SQL*Net message from client               2046

 

SQL>