Monitoring Sessions, Performance, Killing sessinos

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;

________________________________________________________________________________