My Oracle Support Banner

LOB HWM CONTENTION :Using AWR Reports to Identify the Problem; Confirm and Verify the Fix (Doc ID 837883.1)

Last updated on FEBRUARY 17, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 11.2]
Oracle Database - Enterprise Edition - Version to [Release 10.2 to 11.1]
Oracle Database - Enterprise Edition - Version to [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.


LOB performance issues have multiple sources: This note is not designed to be a One-fix for all HWM issues:

This Note is now dated as it was written for 10.x and 11.1.x of the RDBMS - Please use this for research purposes unless on those respective versions.

One of the most prevalent problems seen with LOB performance is when the High WaterMark (HWM) enqueue has notable contention.

COMMENT: There are SEVERAL conditions that can lead to HWM + LOBs during updates or delete/inserts. IF you are on RAC you should also review <Bug 6413373> LOB HW enqueue contention in RAC environments with ASSM space management

Setting the LOB to use Pctversion 100% may be one of the quickest methods to check if the contention can easily be reduced for the LOB

Reducing the segment size is perhaps the strongest recommendation when attempting to improve LOB performance with BASICFILEs. This is usually best accomplished by creating Multiple partitions (powers of 2) to reduce LOB index size and contention. Freepools are also important for RAC configurations.

Securefiles which are available as of 11.2 will relieve most performance issues found with Basicfiles.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.