HW enqueue contention with LOB

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.

One such issue which we faced in one of our environment.

AWR Report

Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 129618 27-Jun-12 01:00:10 240 2.1
End Snap: 129631 27-Jun-12 03:10:18 276 2.1
Elapsed: 130.13 (mins)
DB Time: 1,683.99 (mins)

Cache Sizes

Begin End
Buffer Cache: 7,136M 7,136M Std Block Size: 8K
Shared Pool Size: 1,536M 1,536M Log Buffer: 19,456K

Load Profile

Per Second Per Transaction Per Exec Per Call
DB Time(s): 12.9 0.2 0.01 0.00
DB CPU(s): 0.6 0.0 0.00 0.00
Redo size: 1,512,791.1 24,438.7
Logical reads: 42,623.7 688.6
Block changes: 6,243.5 100.9
Physical reads: 2,725.6 44.0
Physical writes: 364.1 5.9
User calls: 4,557.4 73.6
Parses: 1,646.8 26.6
Hard parses: 0.0 0.0
W/A MB processed: 0.6 0.0
Logons: 0.1 0.0
Executes: 1,647.5 26.6
Rollbacks: 0.8 0.0
Transactions: 61.9

Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: HW - contention 119,796 57,558 480 56.97 Configuration
direct path read 1,293,467 16,330 13 16.16 User I/O
log file sync 554,365 9,730 18 9.63 Commit
db file sequential read 274,737 5,536 20 5.48 User I/O
DB CPU 4,789 4.74

We can see in above report that this is 2 hour report and 57% of DB time is spent on waits for enq: HW -Contention. Other wait events are also related to I/O and this also indicates slow I/O device (Avg wait for db file sequential read is 20 ms, normally you would see 6 ms). We  move to section for "SQL Ordered by Elapsed Time" to see if we can identify sql

SQL ordered by Elapsed Time

Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
63,955.96 958,651 0.07 63.30 0.50 8.76 16w9z88mxsyz3 insert into SCOTT.JBPM4_...
15,336.34 0 15.18 4.81 95.54 7paqx6x20zdy3 JDBC Thin Client SELECT /*+ ORDERED PARALLEL (...

As per AWR report ,sql_id 16w9z88mxsyz3 is taking 64% of total elapsed time for all the sql and this is insert statement with very high execution rate. Digging further into structure of table, we identified that it has 2 clob columns and its residing in tablespace of 4M extent size. We can also look at section "Segments by Physical Writes" ,"Segments by Direct Physical Writes" to identify the segment and notice that they are Lob segments associated with this table. Solution in this case was to hash partition this table (64 partitions) to distribute the inserts into different segments and reduce the number of concurrent requests to allocate extents.

4 thoughts on “HW enqueue contention with LOB

  1. I wonder why 6376915 is not available as a patch for windows. Also it looks like the fix is part of 10.2.0.4, but that is not available for win64… strange.

  2. Yes, bug# 6376915 is fixed in 10.2.0.4.

    For windows this bug is fixed in 10.2.0.3.0 Patch 15 available in 32-Bit Patch 6637235 and 64-Bit (x64) Patch 6637236.

  3. Fix in 10.2.0.4 means, you still have to set the event? If I dont set one, it will not show any better wait times?

    What will be a good number?

    I guess if I have typically Lobs with n Chunks, setting the Value to “2*n” means that the number of requests to that lock will be cut in half. So I can control the number of wait events, however I have to be aware that the times the lock is taken for a single reclaim will be longer (but less often)?

    What would be the Syntax to set that event? Does it need context and level like trace events?

    ALTER SYSTEM SET Event=’44951, level 64′ SCOPE BOTH;

    or something?

  4. Yes, even with the fix in 10.2.0.4 we still have to set the event. Without setting the event the fix will not be in effect.

    Your understanding w.r.t level is correct. It would be hard to suggest an optimal value for the level. I suggest you start with a small value which is close to the average size of your LOB data in a row.

Leave a Reply