Re: Insert contention on RAC

Paul,

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...

    ALTER TABLE ... ALLOCATE EXTENT (INSTANCE 1);
    ALTER TABLE ... ALLOCATE EXTENT (INSTANCE 2);
    ALTER TABLE ... ALLOCATE EXTENT (INSTANCE 3);

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!

-Tim

Quoting "Baumgartel, Paul" <paul.baumgartel_at_credit-suisse.com>:

> To borrow the style from some MetaLink notes--
>
> Fact: Oracle 10.2.0.1.0
> 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
> CREDIT SUISSE
> Information Technology
> Prime Services Databases Americas
> One Madison Avenue
> New York, NY 10010
> USA
> Phone 212.538.1143
> paul.baumgartel_at_credit-suisse.com
> www.credit-suisse.com
>
>
>
> ==============================================================================
> Please access the attached hyperlink for an important electronic
> communications disclaimer:
>
> http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
> ==============================================================================
>

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