Re: enq HW - contention waits

From: Ravi Gaur <ravigaur1_at_gmail.com>
Date: Sat, 19 Apr 2008 19:12:24 -0500
Message-ID: <289232290804191712r6299fec7hd5a9675881ca160a@mail.gmail.com>


Actually we are already using ASSM. I think preallocating extents would be worth trying. I've received several helpful tips and directions to further research into. Thanks for all the help!!

  • Ravi

On Sat, Apr 19, 2008 at 5:35 PM, Yong Huang <yong321_at_yahoo.com> wrote:

> Ravi,
>
> Ric's suggestion is great. Before doing that, you can also try
>
> alter table theBLOBtable modify lob (theBLOBcolumn) (allocate extent
> (instance
> 1));
>
> Remember to include the "instance 1" so space is added below HWM, even if
> you're not using RAC (ignore documentation's caution: only use it on RAC).
>
> Is your tablespace ASSM? If not, consider moving the LOB segment to an
> ASSM
> tablespace because it's more difficult to create HW contention on ASSM.
>
> Yong Huang
>
> > Subject: RE: enq HW - contention waits
> > Date: Fri, 18 Apr 2008 17:41:06 -0500
> > From: "Ric Van Dyke" <ric.van.dyke_at_hotsos.com>
> >
> > 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
> >
> >
> >
> > > 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 --
> > >
> > > ...
> > >
> > > TIA,
> > > - Ravi Gaur
>
>
>
> ____________________________________________________________________________________
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile. Try it now.
> http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Apr 19 2008 - 19:12:24 CDT

Original text of this message