Oracle Wait Interface v$view

Oracle Wait Interface

Oracle Wait Interface has had the following four V$ views :

• V$EVENT_NAME
• V$SESSION_WAIT
• V$SESSION_EVENT
• V$SYSTEM_EVENT

For 10g

Oracle Database 10g Release 1 introduces the following new views to display wait information from several perspectives:

• V$SYSTEM_WAIT_CLASS
• V$SESSION_WAIT_CLASS
• V$SESSION_WAIT_HISTORY
• V$EVENT_HISTOGRAM
• V$ACTIVE_SESSION_HISTORY

V$EVENT_NAME

It is a reference view that contains all the wait events defined for your database instance

select event#, name, parameter1, parameter2, parameter3 from v$event_name order by name;

V$SESSION_WAIT

The V$SESSION_WAIT view provides detailed information about the event or resource that each session is waiting for. This view contains only one row of information per session, active or inactive, at any given time. Unlike the other views, this view displays session-level wait information in real time.

V$SYSTEM_EVENT

The V$SYSTEM_EVENT displays aggregated statistics of all wait events encountered by all Oracle sessions since the instance startup. It keeps track of the total number of waits, total timeouts, and time waited for any wait event ever encountered by any of the sessions.

select b.class, a.*, c.startup_time
from v$system_event a,
v$event_name b,
v$instance c
where a.event = b.name
order by b.class, a.time_waited;

V$SESSION_EVENT

The V$SESSION_EVENT view contains aggregated wait event statistics by session for all sessions that are currently connected to the instance. This view contains all the columns present in the V$SYSTEM_EVENT view and has the same meaning, but the context is session-level. It keeps track of the total waits, time waited, and maximum wait time of each wait event by session.

break on sid skip 1 dup
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 1, 6;