The output in Listing 1 shows that session
3346 is waiting right now for an event:
“enq: TX – row lock contention”—short
for “enqueue for transaction-level lock on
row” or, in plain English, a row-level lock.
The session is waiting because it wants to
lock one or more rows, but another session
has already placed locks on the row or rows.
Unless that other session commits or rolls
back its transaction, session 3346 will not
get the lock it needs and will have no choice
but to wait. On the other hand, the state
of session 2832, “WAITED KNOWN TIME,”
means that it is working—not waiting—
right now. It was, however, waiting earlier
for an event called “SQL*Net message from
client” (I will discuss this specific event
later.) There is one very important lesson
in these results: you cannot look at the
EVENT column alone to find out what the
session is waiting for. You must look at the
STATE column first to determine whether
the session is waiting or working and then
inspect the EVENT column.
After you determine that a session is
waiting for something, the next thing you
need to find out is how long the session
has been waiting. A very long wait usually
indicates some sort of bottleneck. Where
can you get information on the length of the
waiting period? The answer is right there in
the V$SESSION view, in the SECONDS_IN_
WAIT column.
Getting the amount of time a session has
been waiting makes sense for sessions that
are waiting right now, but what about the
sessions that are working now? Recall that
the EVENT column shows not only the event
a session is experiencing now but also the
last wait event the session has experienced.
Another column—WAI T_ TIME—in the same
V$SESSION view shows how long that wait
lasted. (Note that WAIT_TIME is shown in
centiseconds [hundredths of a second].)
Now that you know how to get information on the sessions waiting and working,
let’s put all the information together in a
single query, shown in Listing 2. It clearly
shows the state of the sessions: whether they
are working or waiting; if they are working,
what they were waiting for earlier and for
how long; and if they are waiting, what for
and for how long.
Code Listing 1: Query for displaying sessions, session state, and events
select sid, state, event
from v$session
where username = 'ARUP';
SID STATE EVENT
————— ————————————————————————— ————————————————————————————————————————————
2832 WAITED KNOWN TIME SQL*Net message from client
3346 WAITING enq: TX - row lock contention
Code Listing 2: Query for displaying sessions, session state, and wait details
col "Description" format a50
select sid,
decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from v$session
where username = 'ARUP';
Output:
SID STATE Description
————— —————————— ———————————————————————————————————————————————————————————————————————————————
2832 Working Last waited 2029 secs for SQL*Net message from client
3346 Waiting So far 743 secs for enq: TX - row lock contention
4208 Waiting So far 5498 secs for SQL*Net message from client
IDLE EVENT
Note the details of session 4208 in Listing 2;
it’s currently waiting 5,498 seconds for a
“SQL*Net message from client” event. Recall
from the previous section that an Oracle
Database session can be in one of the three
states: working, waiting for a resource, or
waiting for work. But how can a session
determine whether it is idle? It will expect
to be given work by clients via SQL*Net, but
there is no way for it to know in advance if
any work is coming from the clients. All it
can do is wait for some instruction coming
through SQL*Net. Until then, it will have
nothing else to do but eagerly stare at the
SQL*Net interface, and this condition is
reported as “SQL*Net message from client”
in the V$SESSION view’s EVENT column,
which is practically the same thing as just
being idle.
You can disregard another EVENT column
value, “rdbms ipc message,” because it is an
event status for sessions that are idle. Note
that an idle session does not show IDLE as the
S TATE column value; it still shows “Waiting.”
You have to check the EVENT column to
determine whether the session is truly idle.
