You notice that there is a very high percentage of wait time for the 'enq:HW-contention' event in your RAC database that has frequent insert operations. Which two recommendations may reduce this…

Question

Options

• Ashorter transactions
• Bincreasing sequence cache sizes
• Cusing reverse key indexes
• Duniform and large extent sizes
• Eautomatic segment space management
• Fsmaller extent sizes

• B
  8% (3)
• C
  5% (2)
• D
  84% (32)
• F
  3% (1)

Explanation

Segments have High Water Mark (HWM) indicating that blocks below that HWM have been formatted. New tables or truncated tables [that is truncated without reuse storage clause], have HWM value set to segment header block. Meaning, there are zero blocks below HWM. As new rows inserted or existing rows updated (increasing row length), more blocks are added to the free lists and HWM bumped up to reflect these new blocks. HW enqueues are acquired in Exclusive mode before updating HWM and essentially HW enqueues operate as a serializing mechanism for HWM updates. Allocating additional extent with instance keyword seems to help in non-ASSM tablespace Serialization of data blocks in the buffer cache due to lack of free lists, free list groups, transaction slots (INITRANS), or shortage of rollback segments. This is particularly common on INSERT-heavy applications, in applications that have raised the block size above 8K, or in applications with large numbers of active users and few rollback segments. Use automatic segment-space management (ASSM) and automatic undo management to solve this problem. HW enqueue The HW enqueue is used to serialize the allocation of space beyond the high water mark of a segment. If this is a point of contention for an object, then manual allocation of extents solves the problem.

No community discussion yet for this question.