How to reduce risk of High Watermark enqueue contention for PPM database tables

  • Document ID:KM03157412
  • Creation Date:08-May-2018
  • Modified Date:08-May-2018
    • Micro Focus Products:
      project and portfolio management

This document is under revision.

Summary

This article details on how to reduce risk of High Watermark enqueue contention for PPM database tables.

Question

Problem: PPM database tables have the risk of High Watermark enqueue contention.

Causes: The “enq: HW-contention” wait event is mainly caused by the limitation of basicfiles. PPM database has tables with basicfile lob segments.

Solution: To address the issue, you can try either of the following solutions:

  • Adjust the number of chunks that are cleaned up when chunk cleanup operation is required

    This method is applicable to both ASSM and non-ASSM users.

    Requirement: Your Oracle database is on 11.2.0.2 or later.

    How to: Set event 44951 to a value between 1 and 1024. The event level value identifies the number of chunks to be cleaned up each time a chunk reclamation operation occurs. This can therefore reduce the number of requests for the High Watermark Enqueue.

    SQL> ALTER SYSTEM SET EVENT=”44951 TRACE NAME CONTEXT FOREVER, LEVEL <1 .. 1024>” scope=spfile;

    Note: Database restart is required for it to be effective.

  • Convert basefiles to securefiles
    •  If you use non-ASSM:
    1. Change segment space management of a tablespace from manual to auto, because securefiles require ASSM tablespace.  For details, see https://it.toolbox.com/blogs/surachartopun/how-to-change-segment-space-management-of-a-tablespace-from-manual-to-auto-081510.
    2. Convert basefiles to securefiles. For details, see http://m.blog.itpub.net/17252115/viewspace-774691/.