Re: enq HW - contention waits

The table that shows up on the top (by elapsed time) is actually an INSERT into a table that has a BLOB. However it only has 36 rows (22 extents). The extent size is 64k. The system wasn't quite hanging but slow enough for end users. Is it possible that multiple sessions were actually trying to hit the same segment and trying to extend it?

Thanks,
- Ravi

On Fri, Apr 18, 2008 at 5:41 PM, Ric Van Dyke <ric.van.dyke_at_hotsos.com> wrote:

> 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