This document is under revision.
Summary
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:
- Change segment space management of a tablespace from manual to auto, because securefiles require ASSM tablespace. https://it.toolbox.com/blogs/surachartopun/how-to-change-segment-space-management-of-a-tablespace-from-manual-to-auto-081510. For details, see
- 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/.