Analyzing 'enq: HW - contention' Wait Event (Doc ID 740075.1)

In this Document

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 29-Aug-2016***

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 ADDM, ASH report or v$session_wait query:

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 and tablespace_name = (select name from ts$ where ts#= <parameter2.tablespace#>);

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 <lob_table>
MODIFY LOB (<column_name>) (allocate extent (size <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 >"

~OR~

SQL> Alter system set events '44951 TRACE NAME CONTEXT FOREVER, LEVEL 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

NOTE:9801919.8 - Bug 9801919 - "enq: HW - contention" against segments that add an extent frequently during high concurrency

NOTE:2098543.1 - WAITEVENT: "enq: HW - contention" Reference Note
BUG:8198906 - ORA-00600: [5467] BY SMON WHILE RECOVERING TRANSACTION
BUG:6376915 - ENQ: HW - CONTENTION WITH LOB SEGMENTS
BUG:9711859 - ORA-600 [KTSPTRN_FIX-EXTMAP] DURING EXTENT ALLOCATION
NOTE:837883.1 - LOB HWM CONTENTION :Using AWR Reports to Identify the Problem; Confirm and Verify the Fix
NOTE:6376915.8 - Bug 6376915 - HW enqueue contention for ASSM LOB segments
NOTE:8198906.8 - Bug 8198906 - OERI [kddummy_blkchk] / OERI [5467] for an aborted transaction of allocating extents
NOTE:5768710.8 - Bug 5768710 - ALTER TABLE SHRINK slow with LOB
NOTE:130814.1 - How To Move LOB Data To Another Tablespace
NOTE:365156.1 - Rebuilding LOB freepools
NOTE:1394613.1 - How to Shrink a Securefile LOB Using Online Redefinition (DBMS_REDEFINITION)?
NOTE:1229669.1 - ALERT: Bug 8198906 - Segment header corruption if extent allocation operation is interrupted
NOTE:5636728.8 - Bug 5636728 - LOB corruption / ORA-1555 when reading LOBs after a SHRINK operation
NOTE:9711859.8 - Bug 9711859 - ORA-600 [ktsptrn_fix-extmap] / ORA-600 [kdblkcheckerror] during extent allocation caused by bug 8198906