How to find which objects are causing enq: HW – contention wait event ?

Enqueues are shared memory structures (locks) that serialize access to database resources. They can be associated with a session or transaction.

HW (high water mark) is the boundary between the used and unused areas in the segment.

If a high number of “enq: HW – contention” wait events are observed in AWR reports, the problem needs to be resolved. This usually occurs during insert operations into tables containing LOB fields in basicfile format.

To resolve the issue, the lob field in the relevant table needs to be converted from a basicfile to a securefile.

You can find the problematic objects by using below method :

First, we identify the event IDs related to “enq: HW – contention”

SELECT event_id,
     name,
     parameter1,
     parameter2,
     parameter3
    FROM v$event_name
    WHERE name = 'enq: HW - contention';

 EVENT_ID  NAME 				     PARAMETER            PARAMETER           PARAMETER3
----------  ----------------------   -------------------  ------------------- -------------------
1645217925  enq: HW - contention     name|mode	          table space #       block

Next, we retrieve the necessary information from the dba_hist_active_sess_history view to identify the relevant file and block details associated with the event ID.

SELECT event,
         p1,
         p2,
         p3,
         COUNT (1)
    FROM dba_hist_active_sess_history
   WHERE event_id = 1645217925
GROUP BY event,
         p1,
         p2,
         p3;  2    3    4    5    6    7    8    9   10   11  

EVENT									 P1	    P2	       P3   COUNT(1)
---------------------------------------------------------------- ---------- ---------- ---------- ----------
enq: HW - contention						 1213661190	    25	 17761024	   1
enq: HW - contention						 1213661190	     0	  4294200	  61
enq: HW - contention						 1213661190	    38	853351013     118356
enq: HW - contention						 1213661190	    38	373735013	   1

This information, including file and block details, is identified using the dbms_utility package. The information used in the package is the same as the information in the P3 field of the query above. It is necessary to identify the lobe segment experiencing a high number of waits.

SQL> SELECT DBMS_UTILITY.Data_block_address_file (853351013)      FILE#,
       DBMS_UTILITY.Data_block_address_block (853351013)     BLOCK#
  FROM DUAL;  2    3  

     FILE#     BLOCK#
---------- ----------
       203    1907301

We identify the lob segment here using the datafile and block information.

SQL> SELECT owner, segment_type, segment_name
  2  FROM dba_extents
  3  WHERE file_id = 203 AND 1907301 BETWEEN block_id AND block_id + blocks - 1;

OWNER			       SEGMENT_TYPE	  SEGMENT_NAME
------------------------------ ------------------ ---------------------------------------------------------------------------------
ADURUOZ		       LOB PARTITION	  SYS_LOB0000291772C00006$$

We determine which table the detected lobe segment belongs to from the dba_lobs view.

SQL> SELECT owner,
       table_name,
       column_name,
       chunk,
       securefile
  FROM dba_lobs
 WHERE segment_name = 'SYS_LOB0000291772C00006$$';

OWNER			 TABLE_NAME		 COLUMN_NAME	 CHUNK			 SEC
---------------- --------------- --------------- --------------- ---------------
ADURUOZ		     TESTTABLE       DOSYA			 8192 			 NO

From the output of the above query, the relevant table has been identified, and it is seen that the lob field of this table is not a securefile.

The main reason for this delay is that the LOB file is a basic file. To resolve the issue, the LOB file should be changed to a secure file. If you need help about converting basicfile to securefile , you can read my post named How to convert LOB data stored as basicfile to securefile ?