Friday, January 17, 2014

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>

No comments: