The oracle wait interface

The Oracle Wait Interface (OWI) consists of four dynamic performance views and one system event.

Provides cumulative wait event information for the instance as a whole since instance start-up

SQL> desc v$system_event ;
Name Null? Type
----------------------------------------- -------- ----------------------------
EVENTVARCHAR2(64)
TOTAL_WAITS NUMBER
TOTAL_TIMEOUTS NUMBER
TIME_WAITED NUMBER
AVERAGE_WAIT NUMBER
TIME_WAITED_MICRO NUMBER
EVENT_ID NUMBERWAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)

Provides cumulative wait event information for each process since instance start-up

SQL> desc v$session_event
Name Null? Type
------------------------------------------------- ----------------------------
SID NUMBER
EVENT VARCHAR2(64)
TOTAL_WAITS NUMBER
TOTAL_TIMEOUTS NUMBER
TIME_WAITED NUMBER
AVERAGE_WAIT NUMBERMAX_WAIT NUMBER
TIME_WAITED_MICRO NUMBER
EVENT_ID NUMBER
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)

Provides information about theactive or most recent wait event for each process

SQL> desc v$session_wait
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER
SEQ# NUMBER
EVENTVARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(8)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(8)
P3TEXTVARCHAR2(64)
P3 NUMBER
P3RAW RAW(8)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIMENUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)

Provides a list of all the wait events build into the Oracle kernel and the parameters for each one

SQL> desc v$event_name
Name Null? Type
----------------------------------------- -------- ----------------------------EVENT# NUMBER
EVENT_ID NUMBER
NAME VARCHAR2(64)
PARAMETER1 VARCHAR2(64)
PARAMETER2 VARCHAR2(64)
PARAMETER3 VARCHAR2(64)
WAIT_CLASS_ID...