V$SESSION_EVENT shows how long and how many times each currently connected
session has waited for an event to finish. Thus,
V$SESSION_EVENT is similar to
V$SYSTEM_EVENT, but
V$SYSTEM_EVENT accumulates all figures since the instance started up.
Join with v$session
The following SQL statement joins
v$session_event with
v$session so that it is possible to determine which event consumed the most time of a session:
select
ses.sid,
evt.event,
evt.total_waits,
round(evt.time_waited / 100, 1) time_waited_s,
evt.time_waited_micro,
evt.total_timeouts,
round(evt.average_wait / 100, 1) avg_wait_s,
evt.max_wait,
evt.wait_class,
evt.wait_class#,
evt.con_id
-- evt.event_id,
-- evt.wait_class_id
from
v$session ses join
v$session_event evt on ses.sid = evt.sid
where
ses.username = 'RENE' and
ses.osuser = 'rene@tq84'
order by
evt.time_waited desc
;
Comparing connection time with waiting time and CPU time
At any given point in time, a session is either using CPU cycles or waiting on an event. Thus, the amount of CPU time plus the amount of total waiting time is equal to the total time that a session is connected to Oracle.
The following query selects these three figures. Ideally connected_time_s should be equal to total_waited_time_s + total_cpu_time_s. It turns out, however, that there is a small discrepancy of my idealism of the world we live in.
select
round( (sysdate - ses.logon_time ) * 86400 ) connected_time_s,
round( sum(time_waited_micro) / 1e6 ) total_waited_time_s,
round( prc.cpu_used / 1e6 ) total_cpu_time_s
from
v$session ses join
v$session_event sev on ses.sid = sev.sid join
v$process prc on ses.paddr = prc.addr
where
sev.sid = sys_context('userenv', 'sid')
group by
ses.logon_time,
prc.cpu_used
;