In this Document

Applies to:

Goal

 ***Checked for relevance on 02-Aug-2011*** 

The goal of this article is to explain how to determine which object the 'enq: HW - contention' wait statistic is reported on.

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. If this is the case then manual allocation of extents may resolve the issue

Fix

Given the following situation. This information can be retrieved from a statspack, 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

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';