Most waiting session in Oracle

While working on performance issues of the database. Many times DBA needs to identify the user session which is waiting the most for the resources.

The below query will identify the most waiting sessions for resources.

select sesion.sid,
sesion.username,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$session sesion
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3;


Example:


SQL> select sesion.sid,
  2  sesion.username,
  3  sum(active_session_history.wait_time +
  4  active_session_history.time_waited) ttl_wait_time
  5  from v$active_session_history active_session_history,
  6  v$session sesion
  7  where active_session_history.sample_time between sysdate - 60/2880 and sysdate
  8  and active_session_history.session_id = sesion.sid
  9  group by sesion.sid, sesion.username
 10  order by 3;

       SID USERNAME                       TTL_WAIT_TIME
---------- ------------------------------ -------------
       145                                                                  0
       164     USER1                                             113
       166                                                          66819
       151     TEST                                        4885256


Here we can identify the user session with sid 151 waited for 4885256 seconds.

Good luck.