LOB HWM CONTENTION :Using AWR Reports to Identify the Problem; Confirm and Verify the Fix
Last updated on MARCH 02, 2017
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.1 to 184.108.40.206 [Release 10.2 to 11.1]
Oracle Database - Enterprise Edition - Version 220.127.116.11 to 18.104.22.168 [Release 11.2]
Oracle Database - Enterprise Edition - Version 22.214.171.124 to 126.96.36.199 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 01-Mar-2017***
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.
- This Document will use examples from the Automatic Workload Repository (AWR) and Active Session History (ASH) reports to confirm if you are encountering a problem with HWM contention.
- We will provide one of the possible fixes when you see this HWM enqueue contention on LOBs if using ASSM
- We will then provide you with examples of AWR and ASH reports showing the HWM enqueue before and after the application of the Fix
- Last: we will provide Tips and Techniques on how to diagnose other LOB performance problems using the AWR and ASH reports
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms