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

Last updated on MARCH 02, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.1.0.7 [Release 10.2 to 11.1]
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 01-Mar-2017***


Goal

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.

Solution

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms