Wait Events Drilldown to user

find the code from Natalka Roshak's blog

SQL> select s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    from v$lock l1, v$session s1, v$lock l2, v$session s2
    where s1.sid=l1.sid and s2.sid=l2.sid
    and l1.BLOCK=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l1.id2 = l2.id2 ;

SQL> Select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits)
Sum_Waits
From v$system_wait_class
Group by wait_class
Order by 3 desc;

WAIT_CLASS                SUM(TIME_WAITED)  SUM_WAITS
------------------------- ---------------- ----------
Idle                             206281236 341.202664
Configuration                         3226 65.8367347
Concurrency                           1096 7.55862069
Other                                10177 7.48859455
Administrative                          11        5.5
Commit                                 834 5.27848101
User I/O                             14777 .937984004
System I/O                           23950 .243530429
Application                              7  .02991453
Network                                  1 .000126279

SQL> Select a.event, a.total_waits, a.time_waited, a.average_wait
From v$system_event a, v$event_name b, v$system_wait_class c
Where a.event_id=b.event_id
And b.wait_class#=c.wait_class#
And c.wait_class in ('Application','Concurrency')
order by average_wait desc;

EVENT                          TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
------------------------------ ----------- ----------- ------------
enq: TX - row lock contention          106       21011           30
library cache load lock                 20         186         9.31
os thread startup                      114         897         7.87
latch: library cache                     3           9         2.98
latch: shared pool                       5           4          .76
library cache pin                        1           0          .13
SQL*Net break/reset to client          162           6          .04
latch: row cache objects                 1           0          .04
enq: RO - fast object reuse             72           1          .01
cursor: mutex S                          1           0            0

Since the TX row lock contention is max
To drill down further into session level

SQL> select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait
from v$session_event a, v$session b
where time_waited > 0
and a.sid=b.sid
and b.username is not NULL
and a.event='enq: TX - row lock contention';

To see whether someone is still blocking the session
SQL> Select blocking_session, sid, serial#, wait_class,
seconds_in_wait
From v$session
where blocking_session is not NULL
order by blocking_session;

To identify who the user is
SQL> select sid, serial#, username, osuser, machine from v$session
where username is not NULL;

Another script for finding the rows on which the process is hanging
http://www.remote-dba.cc/oracle_tips_hung.htm