Re: Insert contention on RAC

From: Tim Gorman <>
Date: Wed, 25 Jun 2008 14:03:57 -0600
Message-ID: <>


Nice diagnosis on the "gc buffer busy wait" waits. Performance tuning, especially on RAC, is like peeling an onion. You've peeled away the first layer, now the next is "enq: HW contention"...

Try this...


IIRC, using freelist groups implies that the entire extent is assigned to one of the freelist groups upon allocation. The used to be a parameter called GC_FILES_TO_LOCKS that would do things more granularly, but I'm sure it's deprecated by now. Anyway, automatic allocation of extents makes this assignment to freelist groups and instances a matter of rolling dice, so it might become necessary to pre-allocate extents for each instance.

Try that and see if the high-water mark contention enqueue goes down?

Of course, there will be more layers to the onion under this. I'm betting that, since it sounds like you're inserting (and committing) one row at a time, it'll eventually come down to "log file sync" that proves the ultimate limitation on performance, as the single-row commits drive the each instance's LGWR process nuts. Just my $0.02...

Hope this helps!


Quoting "Baumgartel, Paul" <>:

> To borrow the style from some MetaLink notes--
> Fact: Oracle
> Fact: RedHat 3
> Fact: 3 node RAC
> Many concurrent sessions inserting one row at a time into the same
> table. On first run, table in ASSM tablespace, about 50% of database
> service time was gc buffer busy waits. Built a new tablespace with
> manual segment management, re-created table with three freelist groups
> and 4 freelists. Next run showed >90% of service time was enq: HW
> contention; total run time was slightly higher than it was with ASSM..
> Re-created table again, allocated three 4 GB extents, one for each
> instance. Current run is still showing high HW contention waits.
> Might I have too many freelists? Other than forcing all sessions to use
> the same RAC instance, is there anything else I can do to reduce these
> waits?
> Thanks,
> Paul Baumgartel
> Information Technology
> Prime Services Databases Americas
> One Madison Avenue
> New York, NY 10010
> Phone 212.538.1143
> ==============================================================================
> Please access the attached hyperlink for an important electronic
> communications disclaimer:
> ==============================================================================

Received on Wed Jun 25 2008 - 15:03:57 CDT

Original text of this message