Analyzing 'enq: HW - contention' Wait Event

Analyzing 'enq: HW - contention' Wait Event

SYMPTOMS

1. There is a performance slow down with a large number of waits for 'enq HW - contention'.

2. ASH Reports shows the wait event 'enq HW - contention' which includes SQL with LOB Objects.

CAUSE

The HW enqueue is used to manage the allocation of space beyond the high water mark of a segment. The high water mark of a segment is the boundary between used and unused space in that segment. If contention is occurring for "enq: HW - contention" it is possible that automatic extension is occuring to allow the extra data to be stored since the High Water Mark has been reached. Frequent allocation of extents,  reclaiming chunks, and sometimes poor I/O performance may be causing contention for the LOB segments high water mark.

SOLUTION

How To Analyze the Wait Statistic: 'enq: HW - contention'

Given the following situation. This information can be retrieved from a statspack, ADDM, ASH report or v$session_wait query:

Event	% Wait Time	P1 Parameter	P1 Value	P2 Parameter	P2 Value	Parameter 3	P3 Value
enq: HW - contention	62,81	name|mode	1213661190	table space	4	# block	17005691

1. Determine the correct file and block number:

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(17005691) FILE#,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(17005691) BLOCK#
from dual;

FILE# BLOCK#
---------- ----------
4 228475

For bigfile tablespaces, do not use DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE, or you will get wrong results.
Reference: https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_util.htm#i1002531
In such a case, just use the tablespace# and assume p3 is the block number (there is no relative file number).

2. Determine the object to which this block belongs to

select owner, segment_type, segment_name
from dba_extents
where file_id = 4
and 228475 between block_id and block_id + blocks - 1;


OWNER SEGMENT_TYPE SEGMENT_NAME
--------------- --------------- ------------------------------
SCOTT LOBSEGMENT EMP_DATA_LOB

Additionally, if the lock contention is currrently observed, we can find out the underlying segment using the following query:

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(ID2) FILE#,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(ID2) BLOCK#
from v$lock
where type = 'HW';

As the 'enq HW - contention' may be caused by a number of different reasons, there are also several possible different solutions to alleviate or reduce contention.

Things to check are:-

1. Ensure that your lob segment is not frequently extending.
2. Check I/O performance.
3. A table containing a very busy lob segment may need partitioning in a manner that will evenly distribute concurrent DML across multiple partitions.
4. Frequent lob space/chunk reclaimation can also cause 'enq HW - contention'

In the case of point 4. there are a couple of options that may be able to be employed to provide either temporary relief or a workaround for the problem

a. Manually adding extra space to the LOB segment can alleviate the issue by allocating more free space to the lob segment to chunk reclaimation does not need to take place, until the free space is again used up

ALTER TABLE
MODIFY LOB () (allocate extent (size ));

** The following ALERT should be READ before manually allocating space to a LOB Segment
      - NOTE 1229669.1 Bug 8198906 - Segment header corruption if extent allocation operation is interrupted
b. Using the shrink space command or dbms_redefinition process (for SECUREFILE LOBS) can be used to free up any reclaimable space.

ALTER TABLE test_lob MODIFY LOB (image) (SHRINK SPACE);

** The following documents should be READ before performing a LOB Shrink Operations
      - Bug 5636728 - LOB corruption / ORA-1555 when reading LOBs after a SHRINK operation (Doc ID 5636728.8)
      - Bug 5768710 - ALTER TABLE SHRINK slow with LOB (Doc ID 5768710.8)
c. When using Automatic Segment Space Management (ASSM), and the fix for Bug 6376915 has been applied in your database (Included in 10.2.0.4 +) it is possible to adjust the number of chunks that are cleaned up
when the chunk cleanup operation is required.

This can be enabled by setting event 44951 to a value between 1 and 1024 (default is 1). With the value between 1 and 1024 setting the number of chunks to be cleaned up each time a chunk reclaimation operation occurs. This can therefore reduce the number of requests for the High Watermark Enqueue.

EVENT="44951 TRACE NAME CONTEXT FOREVER, LEVEL < 1 - 1024 >"

Refer to NOTE 6376915.8 "Bug 6376915 HW enqueue contention for ASSM LOB segments"

With Manual Segment Space Management, this value cannot be altered and is fixed at 128.

===============================================================