RE: enq HW - contention waits
Date: Fri, 18 Apr 2008 17:41:06 -0500
When I've run into this in the past the culprit was a table with a LOB and the extent size was way too small. For them many times when they hit the HWM they were also allocating more extents. In their case the entire system was hanging when this happened because it was a very high use table. The table was rebuild with a 256M extent size and they didn't have the problem.
Find table (or other type of segment) that is extending frequently and that might be the issue. You're still going to hit the HWM every now and then, but hopefully you won't be extending the segment at the same time.
Ric Van Dyke
Hotsos Symposium 2009 dedicated to performance and nothing but performance
March 8 - 12, 2009 in Dallas, Texas
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ravi Gaur Sent: Friday, April 18, 2008 4:34 PM
Subject: enq HW - contention waits
We have a production database running on RAC (4-node) (10.2.0.3 on Solaris 10) and I'm noticing a lot of waits on "enq: HW - contention". Doing a bit of researching tells me that it is related to HWM extensions (concurrent inserts). We have no control on the application so I wonder what could be done from the database end to tune this up (if anything). Here's what I see from the AWR during the time it showed up --
Top 5 Timed Events
% Total Call Time
enq: HW - contention
SQL*Net break/reset to client
db file sequential read
gc cr block busy
Any help is deeply appreciated!!
- Ravi Gaur