Important Views.

V$SESSION  and V$SESSION_EVENT.

V$SESSION



______________________________________________________________________________

V$SESSION_EVENT.  : This view displays total of all the waits for all sessions that are currently connected to the instance

V$SESSION_EVENT.

TIMED_STATISTICS has to be true.

Important columns of the view V$SESSION_EVENT.

SID                                   :      ID of the session
EVENT                             :      Name of the wait event
TOTAL_WAITS                  :     Total number of waits for the event by the session.
TOTAL_TIMEOUTS            :     Total number of timeouts for event by the session.
TIME_WAITED                  :     Total amount of time waited for the event by the session (hundred of second)
AVERAGE_WAIT              :      Average amount of time waited for event by the session (hundred of second)
MAX_WAIT                       :      Maximum time waited
EVENT_ID                        :      Identifier for the wait event.
WAIT_CLASS_ID              :      Class of wait event.

_______________________________________________________________________________

The below script utilizes both the views v$session_event and v$session to get proper details.

col sid      format 999
col event    format a39
col username format a6   trunc
select b.sid,
decode(b.username,null,substr(b.program,18),b.username) username,
a.event,
a.total_waits,
a.total_timeouts,
a.time_waited,
a.average_wait,
a.max_wait,
a.time_waited_micro
from  v$session_event a, v$session b
where  b.sid = a.sid + 1
order by 2;
________________________________________________________________________________