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 22.214.171.124 to 126.96.36.199 [Release 11.2]
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 188.8.131.52 [Release 10.2 to 11.1]
Oracle Database - Enterprise Edition - Version 184.108.40.206 to 220.127.116.11 [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.
- 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.
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