enq: HW – contention

While doing a health check for a customer I noticed that enq: HW – contention was high, we had 19.63% of the db time in the AWR report. The analyze showed that it was a limitation of the feature that was used.

Database Info

Oracle 11.2.0.3 on redhat Linux 6

The AWR report:
AWR report

What is an enqueue?

Enqueues are shared memory structures (locks) that serialize access to database resources. They can be associated with a session or transaction.

What is high water mark (HW)

The boundary between used and unused space in a segment.

The analyze is as follows:

Oracle stores information about almost all executions in the database, sql executed, elapsed time, cpu time etc. Wait information is also stored. We can look for that information in historic views.

First we need to find the event id for enq: HW – contention, we do that by querying
v$event_name.

SELECT event_id, 
 name, 
 parameter1, 
 parameter2, 
 parameter3 
FROM v$event_name 
WHERE name = 'enq: HW - contention'; 

  EVENT_ID NAME                 PARAMETER1   PARAMETER2      PARAMETER3
---------- -------------------- ------------ ----------      -------------
1645217925 enq: HW - contention name|mode    table space #   block

Now when we have the event_id we can get the information about the event from dba_hist_active_sess_history.

SELECT event, 
 p1, 
 p2, 
 p3, 
 Count(1) 
FROM dba_hist_active_sess_history 
WHERE event_id = 1645217925 
GROUP BY event, 
 p1, 
 p2, 
 p3;

EVENT                                   P1         P2         P3 COUNT(1)
--------------------- -------------------- ---------- ---------- --------
enq: HW - contention            1213661190         14   58720522     22681
So we get the information from dba_hist_active_sess_history, this view 
contains the history of the contents of the in-memory active session 
history of recent system activity. We can see that we have waited 22681 
times for this event.

Next step is to determine the correct file and block number by using dbms_utility package.

SELECT 
dbms_utility.Data_block_address_file(58720522)  FILE#,
dbms_utility.Data_block_address_block(58720522) BLOCK#
FROM   dual;
      FILE#     BLOCK#
---------- ----------
        14        266

Now we can use this information to get which segment it belongs too.

SELECT owner,
       segment_type,
       segment_name
FROM   dba_extents
WHERE  file_id = 14
       AND 266 BETWEEN block_id AND block_id + blocks - 1;
OWNER          SEGMENT_TYPE      SEGMENT_NAME
-------------  ----------------- -------------------------------------
XXXX          LOBSEGMENT         SYS_LOB0000014653C00008$$

So we can see that this is a lob segment, but we do not which table the segment belongs to. We can find out by checking dba_lobs table.

SELECT owner, 
 table_name, 
 column_name, 
 chunk, 
 securefile 
FROM dba_lobs 
WHERE segment_name = 'SYS_LOB0000014653C00008$$'
OWNER         TABLE_NAME    COLUMN_NAME                  CHUNK SECUREFILE
------------- ------------- ----------------------- ---------- ----------
XXXX          JBM_MSG       PAYLOAD                       8192 NO

So now we have the table and column that uses this lob segment.

The most interesting information here is that we can see that this is not a securefile (=No) but a basic file. What if we search in MOS on basic files and enq. HW – contention ?
We get this information popping up:
Bug 7319120 – HW contention for BasicFile LOBs – special workaround
(Doc ID 7319120.8)

"When concurrent inserts into basic file LOBS suffers a performance bottle
neck due to HW enqueue contention then you have likely hit this issue.
This is really a limitation with basicfile LOBs . This fix provides 
a workaround option which has to be carefully installed and integrated 
into the workload - this workaround can help reduce the HW contention.
Workaround: 
  The preferred option for such contention on basicfile LOBs is to 
  migrate the LOBS to Securefiles in RDBMS 11g or later. Securefile
  LOBs have been designed for better concurrency."

Additional information

2015-08-17, My colleague Peter Kramsu showed me an alternative to convert to securefiles. I still think it is the best solution but it requires some planning and perhaps it cannot be done immediately.

Information comes from Bug 6376915 : ENQ: HW – CONTENTION WITH LOB SEGMENTS

This fix causes ASSM LOB space to batch up reclaim instead of just 
reclaiming the requested/required number of LOB chunks. 
To enable this fix, set event 44951 to the maximum number of chunks 
that you would like to have reclaimed per attempt. 
The maximum allowed is 1024. Anything larger becomes 1024. 
However, if the requested amount of space is larger than the event's
value, the reclaim will be for the requested amount of space.

SQL> ALTER SYSTEM SET EVENT=”44951 TRACE NAME CONTEXT FOREVER, LEVEL <1 .. 1024>” scope=spfile;
NOTE: that a database restart is required for it to be effective.

Conclusion

The limitation of basic files is the main reason for the wait event, convert basic file to secure file should fix the issue.

If you are interested how you can convert a basicfile to a securefile, please see this post

Refrence

Bug 7319120 - HW contention for BasicFile LOBs - special workaround
(Doc ID 7319120.8)
'enq HW - contention' For Busy LOB Segment(Doc ID 740075.1)
How To Analyze the Wait Statistic: 'enq: HW - contention'(Doc ID 419348.1)
Advertisements

2 thoughts on “enq: HW – contention

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s