It is not uncommon for an end-user session
to ?hang? when a user is trying to access a shared data resource that is
being held by another end-user. When a session is waiting on a
resource, information can be found in the v$session view in the
row_wait_file# and row_wait_block# . These values will
help you identify the source of the contention.
Also see
how to find blocking sessions and
how to find
Oracle RAC blocking sessions
If we know the data file and block number,
we can zoom-in and see exactly what resource is causing the contention.
The file number and block number can then be cross-referenced into the
dba_extents view to see the name of the table where the session
is waiting on a block.
Here is the script that I use to find the
source of hung sessions. Note how the v$session column
row_wait_file# is joined into the dba_extents view.
column host format a6
column username format a10
column os_user format a8
column program format a30
column tsname format a12
select
b.machine host,
b.username username,
b.server,
b.osuser os_user,
b.program program,
a.tablespace_name ts_name,
row_wait_file# file_nbr,
row_wait_block# block_nbr,
c.owner,
c.segment_name,
c.segment_type
from
dba_data_files a,
v$session b,
dba_extents c
See code
depot for full script
where
b.row_wait_file# = a.file_id
and
c.file_id = row_wait_file#
and
row_wait_block# between c.block_id and c.block_id +
c.blocks - 1
and
row_wait_file# <> 0
and
type='USER'
;
Here is the output from this script:
BOX USERNAME SERVER OS_USER PROGRAM
------ ---------- --------- -------- ------------------------------
TS_NAME FILE_NBR
BLOCK_NBR OWNER
------------ ---------- ----------
------------------------------
SEGMENT_NAME
---------------------------------------------------------------------------
SEGMENT_TYPE
-----------------
avmak1 JONES DEDICATED server ? @avmak1
(TNS interface)
TS_ITEM 9
55342 SYSADM
ITEM
TABLE
The result shows
that a session owned by user ?JONES? is waiting for a resource in the
TS_ITEM tablespace at data block number 70945. The ITEM table is at data
block 55432.
Now that the source
of the contention has been identified, you can locate other users who
may be holding locks on this table. This is done by changing the WHERE
clause of the above query to include the clause
WHERE row_wait_block# = 5532;
By the way, this
type of wait can commonly be issued when a large update task is holding
locks on the table, or when an individual task has placed an exclusive
lock on specific rows in the table.
If you like DBA internal tricks, check-out
my new book Oracle Tuning: The Definitive Reference by
Rampant TechPress.
[http://dba-oracle.com/include_tuning_book3.htm]
and include the URL for the page.
