Investigating the Sessions - that are hung end in Oracle

Although logs like Alert logs, server-side logs, listener logs, trace files can help you out to indicate the exact reason of a session hang, there is an alternate way to determine this.

There are various situations where in a session of an user would hang due to various environmental situations. It could be due to a shared resource, or inefficient queries on tables without indexes, a massive update statement could be taking its toll, or an user could have placed a lock on some rows e.t.c. In simple terms, it could be this - "when any user accesses a shared data resource held by another one" there is a hang.

Oracle has this very information stored. in In such situations, the V$Session view in the row_wait_file# and row_wait_block# come handy. They help you to determine the session that is waiting on a shared resource.  The file number and the block number can be used toe determine the table on which the wait is happening.

Below is the code snippet:

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'
;
 
The username will tell you the user who is waiting for a lock to be released at the block number. 

To find out the what is blocking use the code snippet below, 

Select blocking_session, sid, serial#, wait_class,seconds_in_wait From v$session 
 where blocking_session is not NULLorder by blocking_session;