Dear Readers,

Trust you are doing well,

Today I am writing this post related to recent issue which I faced after DB upgrade to 19c. We saw sudden increase in waits for event “Enq: HW – contention” wherein HW stands for High Watermark not Hardware 😉

Lets start with some basics:

What is HW ==> High WaterMark:

The high water mark of a segment is the boundary between used and unused space in that segment. So it is the maximum number of blocks which have ever contained data. Deleting records from a table frees up the space but does not move the HWM.

What is Enq: HW – contention:

The HW enqueue is used to serialise the allocation of space beyond the high water mark of a segment. If lots of data is being added to an object concurrently, then multiple processes may be trying to allocate space above the high water mark at the same time, leading to contention.

So in my case, there was a insert query on a table with a basic file LOB segment. This query started waiting on enq: HW contention intermittently post 19c upgrade. As issue was occurring intermittently, I was sure it was reaching to its HW more frequently causing possible contention. This contention was on LOB segments not of actual table. You can easily find this out by checking row_wait_obj# associated with session that is waiting.

As a immediate solution, I tried to pre-allocate some extents to LOB manually if this helps us resolving issue:

alter table APP_USER.APP_USER_SESSION modify lob (USER_SESSION_DATA) (allocate extent (size 200M));

This didn’t give me much relief. So I tried pre-allocating extents to LOB with bumping up HWM of lob explicitly.

alter table APP_USER.APP_USER_SESSION modify lob (USER_SESSION_DATA) (allocate extent (size 200M instance 10000));

This definitely helped me reducing enq: HW contention waits to some extent.

I had proposed long term solution to this issue to convert basic file lob to secure file lob, which was moved to production post rigorous testing. Oracle recommendes to use SecureFiles LOBs as it improves performance and also supports compression, deduplication, and encryption features.

enq: HW contention just disappeared from TOP wait events, post converting problematic lob into SecureFiles. I saw almost 74% reduction in total waits associated with enq: HW contention.

Below graph shows overall improvement, change was implemented on 18th April 2022.

Hope u will find this post very useful 🤗

Cheers

Regards,
Adityanath