In this Document

Applies to:

Symptoms

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

2.Using the following note Note 419348.1 "How To Analyze the Wait Statistic 'enq HW - contention' " a LOB object is identified as the object where the wait is occurring.

Cause

Frequent allocation of extents,  reclaiming chunks, and sometimes poor I/O performance may be causing contention for the LOB segments high water mark.

Solution

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.

References