Listing 4 shows a SQL script that enables
you to select the specific blocking row from
the table with the information gathered so
far. Save this script in a file named rowinfo
.sql. The script expects the input in the following order: owner, table name, object#,
file#, block#, and row#. You can call this
script and pass all the requested parameters
by copying and pasting the corresponding
output from Listing 3.
The output in Listing 4 shows the specific
row on which a lock is being requested but
that is locked by another session. So far you
have identified not only the source session
of the locking but the specific row being
locked as well.
Is it possible that the session holding the
lock (SID 2832) is somehow disconnected
from the client? That can occur in connection pools or when users access the database
with thick-client tools such as Oracle SQL
Developer. After you identify the session
holding the lock, you may want to wait until
it commits or rolls back the transaction.
Either action releases the lock.
In the case of a dead connection, you
may alternatively decide to kill the session,
which will force a rollback releasing the
locks held by the blocking session and
enabling the waiting sessions to continue.
Occasionally the problem can be pretty
simple: for instance, someone issued an
UPDATE statement from a thick-client tool
but forgot to commit and thus caused every
session to wait for those updated rows.
Identifying that blocking session enables
you to send a gentle reminder to rectify that
situation immediately.
Code Listing 5: Sessions from a specific user
select SID, osuser, machine, terminal, service_name,
SID OSUSER MACHINE TERMINAL SERVICE_NAME LOGON_TIME LAST_CALL_ET
————— ———————— —————————— ——————————— ————————————————— —————————————— —————————————————
3346 oradb prodb1 pts/5 SYS$USERS 05-FEB- 12 6848
2832 oradb prodb1 pts/6 SERV1 05-FEB- 12 7616
4408 ANANDA ANLAP ANLAP ADHOC 05-FEB- 12 0
OSUSER. The operating system user as which the client is connected. The output indicates that session 4408
is connected from the ANLAP machine, where a Windows user, ANANDA, has logged in.
MACHINE. The name of the machine where the client is running. This could be the database server itself. For
two of the sessions, the machine name shows up as “prodb1.” Session 4408 runs on a different machine—
ANLAP—presumably a laptop.
TERMINAL. If the session is connected from a UNIX server, this is the terminal where it runs.
LOGON_TIME. This shows when the session was first connected to the Oracle Database instance.
LAS T_CALL_ET. This shows when the session last issued some SQL. The output indicates that session 3346
made its last SQL call 6,848 seconds ago.
Code Listing 6: Session waits for a specific machine
col username format a5
col program format a10
col state format a10
col last_call_et head 'Called|secs ago' format 999999
col seconds_in_wait head 'Waiting|for secs' format 999999
col event format a50
select sid, username, program,
last_call_et, seconds_in_wait, event
from v$session
where machine = 'appsvr1'
/
MORE ON THE SESSION
In many troubleshooting situations, just
knowing the SID of each session is not
enough. You may need to know other details,
such as the client machine the session is connecting from, the user (of both the database
and the operating system), and the service
name. All of this information is also readily
available in the same V$SESSION view you
have been using. Let’s briefly examine the
columns that provide that information, by
running the script shown in Listing 5.
Using the columns shown in Listing 5,
you can get very detailed information on a
Called Waiting
SID USERNAME PROGRAM STATE secs ago for secs EVENT
————— ——————————— ———————————————— —————————— ———————————— ———————————— ————————————————————————————
2832 ARUP sqlplus.exe Waiting 152 151 SQL*Net message
from client
3089 ARUP sqlplus.exe Waiting 146 146 enq: TX - row lock
contention
3346 ARUP sqlplus.exe Working 18 49 SQL*Net message
from client
user’s sessions.
Suppose you receive a complaint that
the applications running on the application server named appsvr1 are experiencing
performance issues. Listing 6 shows a query
against the V$SESSION view—including
columns you’ve used in previous queries in
this article—for the sessions connected from
that machine and the output.
From the output, you can easily see
that three sessions are connected from
the appsvr1 application server. All of them
are running SQL*Plus (as shown in the
PROGRAM column). SID 3346 is the only one
that is working (indicated by “Working” in
the STATE column). Because it’s working,
the EVENT column shows the last time the
session waited. The wait time in this case
is meaningless, because the session is not
waiting but actually working. The “Called
secs ago” column (representing the “last_
call_et” column in V$SESSION) displays 18,
