In this Document

Applies to:

Goal

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

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.

Fix

We will focus on Bug 6376915 "HW enqueue contention for ASSM LOB segments" and applying the fix:

“…HW enqueue contention can occur for LOB segments which are ASSM managed as space allocation only acquires one block at a time. With this fix ASSM lobs get a minimum number of chunks based on the value of event 44951 (up to 1024) which should help reduce or eliminate HWM enqueue contention.

This is the most important LOB HWM Enqueue bug encountered with the following prerequisites
  a) Automatic Segment Space Mangement (ASSM) is used with the tablespace storing the LOB
  b) Inserts and updates are slow (vs read)
  c) High Load and/or concurrency exists
  d) HW: enqueue is seen in the top 5 wait events of the AWR report
  e) Not using RAC or Securefiles

FIX

1) The fix is already included in 11.1.0.7 and 10.2.0.4/10.2.0.5 but not implemented until the event is enabled
 - for 10.2.0.3 Download and apply the patch for BUG 6376915:

2)  Set EVENT 44951 up to 1024 (which changes Chunks allocated from 1 on ASSM to 1024)

NOTE: The fix for this problem introduces a *mandatory event* which must be set in order for the ASSM architectural change leading to more efficient allocation of LOB chunks during high load and transaction contention when Extent allocation is needed. The event is *not* set even after you apply the patch.

      ALTER SYSTEM SET EVENT='44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024'   scope=spfile;

 - or set directly in the pfile/init.ora -

    EVENT="44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024"

 3) If using the 10.2.0.3 patch then Shutdown and Restart the database

 COMMENT: MONITOR the impact of this event as it may introduce performance degradation in some cases: As the event is dynamic once the fix is in place you can enable at various levels or disable using level 0. Common levels include 128,512 or 1024.

USING AWR REPORTS to : 

1. Confirm you are hitting the LOB + enq: HWM - Contention Problem
2. Confirm if the Patch + EVENT set resolved the problem
3. Confirm if the problem did not help HWM contention  

The following three Symptoms will help you identify if you have encountered the problem

Symptom #1

AWR REPORT

*  "enq: HW - contention" will be in the Top 5 Wait events 
** "SQL*Net more data from client" is usually in the Top 5  Wait events also 

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ 
                                                     AVG
                                                     wait     Total  Call Wait
Event                              Waits    Time(s)  (ms)     Time%  Class
------------------------------ ------------ ----------- ------ ------ ---------
db file sequential read        2,580,474    35,544    14       77.4  User I/O
SQL*Net more data from client    659,140     5,513     8 **    12.0  Network
CPU time                                     4,540   9.9
enq: HW - contention              88,910     2,890    33 *     6.3   Configuration
log file sync                    777,146     1,688     2       3.7   Commit

Symptom #2

Review the TOP Enqueue Activity:

• The HWM enqueue will be among the top 5
• Transaction Contention may also be in the top few enqueues
• High Total and Average Wait times for Segment High Water Mark and TX row-lock contention
• High Transaction Waits in fractional or complete seconds (user timeouts or long commits

-- enq: TX - row lock contention hit 2.73 seconds Per Transaction !

Symptom #3

AWR WAIT EVENTS: More evidence

• enq: HW - contentionSQL
• Net more data from client **
• enq: TX - row lock contention ***
• High Average Wait in ms (20ms or higher) *
• Transaction Time in fractional (or complete) seconds * 

...
-- enq: TX - row lock contention hit 1.46 seconds Per Transaction !

POST PATCH; EVENT; BOUNCE and AWR

TOP 5 TIMED EVENTS : you should notice the following changes

• Enq HWM - contention             -- this event is usually missing from the top 5 post patch or should show as less than ~6ms avg/read if this bug is the HWM problem! 
• SQL*Net more data from client -- this events time will usually decrease dramatically or drop from the top 5
  ------ ------ ---------

TOP ENQUEUE ACTIVITY: you should notice the following changes

• The HWM enqueue will be move far down the list and may drop off measurable enqueue activity
• Transaction Contention should no longer be in fractional or higher seconds
• Total and Average Wait times for Segment High Water Mark and TX row-lock contention should be a fraction of previous assessed times
• AWR WAIT EVENTS to Monitor after the PATCH has been applied, the EVENT is set and the Instance has been bounced include: 

AWR WAIT EVENTS to Monitor after the Patch, Event and Bounce has occurred:
 

Missing or notably decreased from the WAIT EVENTS section of the AWR report after the patch:

CONFIRMING THE EVENT IS SET

The fix for BUG 6376915 requires:

1. Patch    (already built into 10.2.0.4,10.2.0.5 and 11.1.0.7 or higher)
2. EVENT 44951 set in the spfile of init.ora
3. LEVEL for the event: this value can be set up to 1024

          e.g. EVENT=44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024


Many times you may believe that this event is already set in the database or for the instance: However, experience has shown that this is not always the case.
The easiest method to determine if the event has been set is to look at the most recent startup in the ALERT.LOG which reports Non-Default values.

ALERT.LOG

 LAST:

There are several different sources for performance issues with LOBs. This event is only one of many potential problem source/solutions.

Under some circumstances this event will increase HWM contention so it is important to qualify and test the fix. The AWR reports can be used to provide feedback on any changes made.

References