This document is under revision.
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 22.214.171.124 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:
- 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.
- Convert basefiles to securefiles. For details, see http://m.blog.itpub.net/17252115/viewspace-774691/.
- ââIf you use ASSM, directly convert basefiles to securefiles. For details, see http://m.blog.itpub.net/17252115/viewspace-774691/.