AskDba.org Weblog

Definition
Enqueues are local locks that serialize access to various resources. Enqueue wait event indicates a wait for a lock that is held by another session (or sessions) in an incompatible mode to the requested mode.

HW Enqueue – High Watermark enqueue is acquired when a segment’s high water mark is moved, which will normally happen during a heavy insert operation on the segment.

Detailed explanation
The HW enq is used to move the segment hwm and also to reclaim the space in lob segments.HW enqueue is required for extending segments in locally managed as well as dictionary managed tablespace. The period for which it is held will be shorter for locally managed tablespaces but we do hold it to serialize extensions.

When lobs are deleted or updated, space freed up is not reclaimed immediately. Deleted lob chunks are maintained in the lob index segment for purpose of CR – the committed free space is reclaimed from the lob index when the free space in the lob-base segment is exhausted. Delayed reclaimation is indirectly controlled by PCTVERSION or RETENTION. Movement of free space from lob index to lob-base segment is done under hw enqueue.

When inserts happen, space for new rows are satisfied in the following orderly ways:

a. Allocate free blocks from the segment directly (these blocks are not mapped by the Lobindex).

b. Reclaim freed blocks (from previous updates/ deletes, the ones mapped by the lobindex) that are not needed to satisfy PCTVERSION or RETENTION. In extreme cases, reclaimed blocks even if it means not being able to guarantee RETENTION. This requires HW-enqueue on the lobindex segment.

c. Extend the segment.

To Identify HW – contention
Finding Blockers:

If a session is stuck waiting on an “enqueue” wait use View V$LOCK to find the blocker/s:

SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;

To view the waiters:

SELECT * FROM v$lock WHERE request > 0;

Description:

* V$LOCK.TYPE is the lock type from P1
* V$LOCK.ID1 is the lock ID1 from P2
* V$LOCK.ID2 is the lock ID2 from P3

In v$lock Id1 is the tablespace number in which the segment (that is having its high water mark changed) resides and ID2 is the tablespace relative DBA of the segment header.

To identify the segment on which HW enqueue wait is being observed, run the following:

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

select owner, segment_type, segment_name
from dba_extents
where file_id = <>
and <> between block_id and block_id + blocks – 1;

Reducing/ Resolving Contention:

1. Recreate the objects with the freelist groups and preallocate extents using ALTER TABLE…ALLOCATE EXTENT. This will help improve performance

2. Review the freelist and freelist group setting. If it is too high – we may be moving more blocks to freelists and hold segment hw for a longer period of time since it takes time to format all the blocks.

If preallocation does not work, increase the number of free lists. The speed of high water mark movement depends on the number of free lists.

3. If this is during lob space reclamation, review the pctversion setting. Depending on the usage of the blob you can set pctversion to 0 or small values to avoid space reclamation. The default is 10%.

4. Most issues with HW contention on LOBs and space reclaimation have been resolved by applying patch# 6376915 and setting the event 44951.

This fix causes ASSM LOB space to batch up reclaim instead of just reclaiming the requested/required number of LOB chunks. To enable this fix, set event 44951 to the maximum number of chunks that you would like to have reclaimed per attempt. The maximum allowed is 1024. Anything larger becomes 1024. However, if the requested amount of space is larger than the event’s value, the reclaim will be for the requested amount of space.

5. Other considerations:
a. Partitioning the LOB segment.
b. Increasing the chunk size.
c. Rebuild the table or truncate the table.