Waits on High Water enqueue occur when allocating space beyond the high water mark of a segment during insert operations. Also, this wait occurs when reclaiming free space in lob segments after delete or update operations. When lob segments are deleted or updated, the space being used is not immediately freed up so consistent reads can be maintained. When more space is needed, a HW enqueue lock is acquired to reclaim the free space from the previous deletes or updates. The PCTVERSION or RETENTION settings on the log segment object control the frequency of when the space is reclaimed.
For this event, in the V$SESSION_WAIT table, the P2 column contains the tablespace number and the P3 column contains the relative data block address of the segment header of the object for which space is being allocated.
Solutions
If possible, move objects to ASSM (Automatic segment space management) tablespace.
If using manual space management, recreate objects increasing freelists and freelist_groups. However, make sure they aren’t set too high as this might cause longer HW enqueue waits as more blocks are being moved and formatted.
Pre-allocate extents so inserts do not wait on the serialization of allocating additional extents – ‘alter table table_name allocate extent;’
If heavily updating or deleting LOB segments, evaluate the setting of PCTVERSION on the LOB segment.PCTVERSION is the percentage of all used LOB data space that can be occupied by old versions of LOB data (default is 10%). If your application performs many LOB updates concurrent with heavy reads of the LOB column, then consider increasing PCTVERSION to 20%. This will allow more free pages to be used for older versions of the LOB column in order to maintain consistent reads. If LOB data is infrequently updated, consider setting PCTVERSION to a lower value such as 5% or to 0% if the LOB is read-only.
Alternatively, include the RETENTION clause on the LOB storage statement to keep a ‘period of time’ for old versions. When the RETENTION clause is used, the amount of time for retention is determined by the UNDO_RETENTION parameter. NOTE: Only PCTVERSION or RETENTION can be specified on the storage clause not both.
Consider partitioning the LOB segment.
Increase the LOB segment chunk size.
Consider changing the hidden parameter ‘_bump_highwater_mark_count’. This parameter determines how many blocks are allocated per free list when advancing HWM.
Review Bug 6376915- HW enqueue contention for ASSM LOB segments and apply patch if needed.When using Automatic Segment Space Management (ASSM) tablespaces, HWM contention can occur for LOB segments as ASSM LOB space allocation only acquires one block at a time.This patch causes ASSM LOB space to batch up the reclaiming of space. With this fix ASSM LOBs get a minimum number of chunks based on the value of event 44951 (maximum setting is 1024).
Leave a Reply