Examining Session Waits

select event, count(*)"Current Total" from v$session_wait
group by event order by "Current Total" desc

Now we further investigate locks held by application using fowllowing query

select event, count(*) waits from v$session_wait
where event = 'enq: TX - row lock contention'
group by event order by waits desc

One session is waiting for an enqueue lock, possibly because of a blocking lock held by another session. If you see a large number of sessions experiencing row lock contention, you must investigate further and identify the blocking session.

Here’s one more way you can query the V$SESSION_WAIT view, to find out what’s slowing down a
particular session:


SELECT SID,SESS_SERIAL#, WAIT_EVENT_TEXT ,BLOCKER_SID,BLOCKER_SESS_SERIAL# FROM V$SESSION_BLOCKERS

SID         SESS_SERIAL#    WAIT_EVENT_TEXT                BLOCKER_SID      BOCKER_SESS_SERIAL#
406         1643                  enq: TX - row lock contention         930                      773

****************************************

select event, state, seconds_in_wait siw
from v$session_wait
where sid = 406;


EVENT                                                  STATE                  SIW

enq: TX - row lock contention               WAITING               836

So session 406 is waiting for 836 seconds.for an enqueue event,
because the row (or rows) it wants to update is locked by another transaction

Now as we session 930 which was blocking session 406 commits its transaction lock is released. After that we again executed same query and wait event changed because lock is released and session 406 wait event changed.


The V$SESSION_WAIT view shows the current or the last wait for each session. The STATE column in
this view tells you if a session is currently waiting. Here are the possible values for the STATE column:

WAITING: The session is currently waiting for a resource.
WAITED UNKNOWN TIME: The duration of the last wait is unknown (this value is
shown only if you set the TIMED_STATISTICS parameter to false).
WAITED SHORT TIME: The most recent wait was less than a hundredth of a second
long.
WAITED KNOWN TIME: The WAIT_TIME column shows the duration of the last wait.
Note that the query utilizes the seconds_in_wait column to find out how long this session has been
waiting. Oracle has deprecated this column in favor of the wait_time_micro column, which shows the
amount of time waited in microseconds. Both columns show the amount of time waited for the current
wait, if the session is currently waiting. If the session is not currently waiting, the wait_time_micro
column shows the amount of time waited during the last wait.