Resolving HW enqueue contention (Slow Inserts on Table having BLOBs,10g) Posted On: January 08, 2013

    Resolving HW enqueue contention (Slow Inserts on Table having BLOBs,10g)

     

    This article is also posted at a best Oracle DBA Guru Riyaj Shamsudeen website :-

    http://orainternals.wordpress.com/2008/05/16/resolving-hw-enqueue-contention/

     

    Issue:-

    When you see slow inserts on table having BLOBs. From AWR report if you see a high number of enq: HW – contention.

     

    Findings:-

    HW enqueue

    When a session needs access to a resource, it requests a lock on that resource in a specific mode. Internally, lock and resource structures are used to control access to a resource. Enqueues, as name suggests, have First In First Out queueing mechanism.

     

    Segments have High Water Mark (HWM) indicating that blocks below that HWM have been formatted. New tables or truncated tables [ that is truncated without reuse storage clause ], have HWM value set to segment header block. Meaning, there are zero blocks below HWM. As new rows inserted or existing rows updated (increasing row length), more blocks are added to the free lists and HWM bumped up to reflect these new blocks. HW enqueues are acquired in Exclusive mode before updating HWM and essentially HW enqueues operate as a serializing mechanism for HWM updates.

    In non-ASSM tablespaces, HWM is bumped up by 5 blocks at a time ( Actually, undocumented parameter _bump_highwater_mark_count controls this behavior and defaults to 5). Heavy inserts in to a table can result in increased HWM activity leading to HW enqueue contention. This issue is prevalent if the table has LOB columns or if the row length is big.

     

    You can trace the session by enabling these traces with a famous tool

    waitprof.sql:-

    http://blog.tanelpoder.com/2008/06/21/another-use-case-for-waitprof-diagnosing-events-in-waitclass-other/

     

    alter session set events ’10704 trace name context forever, level 15′;

    alter session set events ’10046 trace name context forever, level 8′;

     

     

     

     

    References:-

    ‘I/O Slave Wait’ Wait Event Reported as Top 5 Waited Events [ID 1300021.1]

    LOB HWM CONTENTION :Using AWR Reports to Identify the Problem; Confirm and Verify the Fix [ID 837883.1]

     

    Solution:-

    Set one of these events and see if the timings improve and check the waits. You can not set both the events.

    ALTER SYSTEM SET EVENT=’44951 TRACE NAME CONTEXT OFF’ scope=spfile;

    ALTER SYSTEM SET EVENT=’44952 TRACE NAME CONTEXT FOREVER, LEVEL 1′ scope=spfile;

    Rebuild the indexes.

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>