DIAGNOSIS OF LOCKING
The output of Listing 2 provides enough
information to enable you to make a diagnosis about the performance of these three
sessions. Session 4208 is idle, so any complaints that session 4208 is slow just aren’t
related to the database. Any performance
issues related to this session could be related
to a bug in the code that’s going through an
infinite loop or high CPU consumption on
the application server. You can redirect the
performance troubleshooting focus toward
the application client.
The story of session 3346 is different.
This session is truly a bottleneck to the
application. Now that you know why this
session appears slow—it is waiting for
a row lock—the next logical question is
which session holds that lock. The answer is
also found in—I hope you guessed it—the
V$SESSION view, in, more specifically, the
BLOCKING_SESSION column. (Note that in
an Oracle Real Application Clusters [Oracle
RAC] environment, the blocking session
may exist in a different instance. In such
a case, the blocking instance is displayed
in the V$SESSION view’s BLOCKING_
INS TANCE column.)
You can find out the blocking session
and instance by issuing the following SQL
statement:
select
blocking_session B_SID,
blocking_instance B_Inst
from v$session
where sid = 3346;
Code Listing 3: Getting row lock information
select row_wait_obj#,
row_wait_file#,
row_wait_block#,
row_wait_row#
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
——————————————————— ———————————————————— —————————————————————— ———————————————————
To get the object information:
select owner, object_type, object_name
from dba_objects
where data_object_id = 241876;
OWNER OBJECT_TYPE OBJECT_NAME
——————— ———————————————— ————————————————
ARUP TABLE T1
Code Listing 4: Finding the row information
REM Filename: rowinfo.sql
REM This shows the row from the table when the
REM components of ROWID are passed. Pass the
REM following in this exact order
REM 1. owner
REM 2. table name
REM 3. object_id
REM 4. relative file ID
REM 5. block ID
REM 6. row Number
REM
select
from & 1..& 2
where rowid =
dbms_rowid.rowid_create (
rowid_type => 1,
object_number => & 3,
relative_fno => & 4,
block_number => & 5,
row_number => & 6
)
/
B_SID B_INST
—————— ———————
2832 1
SQL> @rowinfo ARUP T1 241876 1024 2307623 0
COL1 C
————— —
1 x
The output shows clearly that SID 2832 is
holding the lock that SID 3346 is waiting for.
Now you can follow a cause/effect relationship between the session in which an update
to a row is being blocked and the session that
holds the lock on that row.
You can find the specific row that is locked
by first finding the table containing that row.
To find that table, use the same V$SESSION
view; in this case, the information is in the
RO W_WAI T_OBJ# column, which shows
the object number of the table whose row is
being locked. You can then get the name of
the table from the DBA_OBJEC TS view, using
this object number, as shown in Listing 3.
