Finding the Cause of Hung End-User Sessions in Oracle

Resolving The Problem

It's not uncommon for end-user sessions to "hang" when they're trying to grab a shared data resource that's held by another end user. The end user often calls the help desk trying to understand why they can't complete their transaction, and the Oracle professional must quickly identify the source of the contention.

Whenever Oracle has a session waiting on a resource, this information can be found in the V$SESSION view in row_wait_file# and row_wait_block#.

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's the script. Note how v$sessionrow_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
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's the output from this SQL*Plus 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)
IRMS_D 9 70945 SYSADM
CUSTOMER_VIEWS40
TABLE

We can see that a session owned by user "JONES" is waiting for a resource in the IRMS_D tablespace at data block number 70945. At this Oracle data block, we find the CUSTOMER_VIEWS40 table.

After you've identified the source of the contention, you can then locate other users who may be holding locks on this table. 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.
Display Oracle sessions that are waiting for block access
One of the most perturbing problems with Oracle is finding sessions that are waiting for access to a table segment header. Whenever an Oracle table row is inserted, Oracle must go to the first block in the table to grab a freelist to get a free data block to place the row.

When the segment header is being used by another task, a "buffer busy wait" occurs. A buffer busy wait is commonly found when a table has multiple update tasks and not enough freelists in the segment header. Buffer busy waits are very transient, but they can add up to a real performance problem. Here's the script that I run every five minutes when I suspect that a table does not have enough freelists.

prompt
prompt
prompt **********************************************************
prompt Session summary by tablespace Section
prompt **********************************************************
prompt This is a summary of activity by tablespace
prompt high buffer busy waits may indicate the need for more freelists


column c0 heading 'tablespace' format a14;
column c1 heading 'event' format a25;
column c2 heading 'tot waits' format 999,999;
column c3 heading 'tot timeouts' format 999,999;
column c4 heading 'avg waits' format 999,999;

break on c0 skip 1;

select distinct
b.tablespace_name c0,
a.event c1,
sum(a.total_waits) c2,
sum(a.total_timeouts) c3,
sum(a.average_wait) c4
from
v$session_event a,
dba_data_files b,
v$session c
where
a.sid = c.sid
and
b.file_id = c.ROW_WAIT_FILE#
and
(
average_wait > 0
or
total_timeouts > 10
)
and
total_waits > 1000
group by
tablespace_name,
event
order by
b.tablespace_name,
sum(total_waits) desc
;