"enq: HW - contention", small tool to find involved segment

Analyzing ASH, every time I find "enq: HW - contention" wait, the most important answer is-which segment is in case. Look in example:

Top User Events

Event	Event Class	% Event	Avg Active Sessions
CPU + Wait for CPU	CPU	62.89	20.15
enq: HW - contention	Configuration	23.74	7.61
db file sequential read	User I/O	3.18	1.02
log file sync	Commit	1.84	0.59
log buffer space	Configuration	1.32	0.42

which is follow by (in same ASH report):

Top Event P1/P2/P3 Values

Event	% Event	P1 Value, P2 Value, P3 Value	% Activity	Parameter 1	Parameter 2	Parameter 3
enq: HW - contention	23.74	"1213661190","21","201377283"	23.70	name|mode	table space #	block
db file sequential read	3.24	"48","101101","1"	0.01	file#	block#	blocks
log file sync	1.84	"520","2876838558","0"	0.01	buffer#	sync scn	NOT DEFINED
log file parallel write	1.73	"1","468","2"	0.01	files	blocks	requests
direct path read	1.28	"26","1334118","1"	0.01	file number	first dba	block cnt

For all cases with big "WF - contention" wait I use this small but useful procedure. Here is the code:

CREATE OR REPLACE PROCEDURE hw_wait (p_p3 IN PLS_INTEGER
                                    )
AUTHID CURRENT_USER
IS
/* ---------------------------------------------------------------------------

 Filename: hw_wait.prc
 CR/TR#  :
 Purpose : Find object that cause enq: HW - contention

 Date    : 12.09.2013.
 Author  : Damir Vadas, damir@vadas.hr

 Remarks : Tested on 11g only (should work on lower versions)
           grant select on sys.dba_extents to ;
           grant select on sys.dba_lobs to ;
           grant select on sys.dba_tables to ;

 Changes (DD.MM.YYYY, Name, CR/TR#):
--------------------------------------------------------------------------- */
  l_block# PLS_INTEGER;
  l_file#  PLS_INTEGER;
  l_owner  VARCHAR2(128 CHAR); 
  l_segment_type VARCHAR2(128 CHAR);
  l_segment_name VARCHAR2(128 CHAR);
  l_table_name   VARCHAR2(128 CHAR);
  l_column_name  VARCHAR2(128 CHAR); 
BEGIN
  select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(p_p3) FILE#,
         DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(p_p3) BLOCK#
    into l_file#,
         l_block#
    from dual
  ;
  select owner,    segment_type,  segment_name
    into l_owner,l_segment_type,l_segment_name
    from sys.dba_extents
   where file_id = l_file#
     and l_block# between block_id and block_id + blocks - 1
  ;
  CASE 
    WHEN l_segment_type= 'LOBSEGMENT' THEN  
      SELECT table_name  , column_name
        INTO l_table_name, l_column_name
        FROM sys.dba_lobs
       WHERE segment_name = (SELECT object_name
                               FROM dba_objects
                              WHERE owner = l_owner AND object_name = l_segment_name
                           )
      ;
      dbms_output.put_line(l_owner || '.' || l_table_name || '-' || l_column_name);
    WHEN l_segment_type IN ('TABLE','INDEX') THEN
      dbms_output.put_line(l_owner || '.' || l_segment_name); 
    ELSE
      dbms_output.put_line('analyze for : '||l_owner||'.'||l_segment_type||'-'||l_segment_type||' not implemented.');
  END CASE;    
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END HW_wait;
/

Result is:

SQL> set serveroutput on size 1000000;
SQL> exec HW_WAIT(201377283);  -- parameter is P3 value from ASH second table!
MATS.DF00_MESSAGE_QUEUE_TBL-"USER_DATA"."TEXT_LOB"

PL/SQL procedure successfully completed.

SQL>

The End

Please be aware that only some segment types are covered so if you involve with some new you might have to add your code for that.