RE: enq HW - contention waits

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 Enterprises

Hotsos Symposium 2009 dedicated to performance and nothing but performance

March 8 - 12, 2009 in Dallas, Texas

Be there.

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ravi Gaur Sent: Friday, April 18, 2008 4:34 PM
To: oracle-l
Subject: enq HW - contention waits  

Greetings,

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

Event

Waits

Time(s)

Avg Wait(ms)

% Total Call Time

Wait Class

CPU time  

7,148  

43.5  

enq: HW - contention

67,034

5,263

79

32.0

Configuration

SQL*Net break/reset to client

61,006

1,504

25

9.2

Application

db file sequential read

152,431

885

6

5.4

User I/O

gc cr block busy

34,315

490

14

3.0

Cluster

Any help is deeply appreciated!!

TIA,
- Ravi Gaur

--
http://www.freelists.org/webpage/oracle-l