Re: Insert contention on RAC
- From: Tim Gorman <tim@xxxxxxxxx>
- To: paul.baumgartel@xxxxxxxxxxxxxxxxx
- Date: Wed, 25 Jun 2008 14:03:57 -0600
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@xxxxxxxxxxxxxxxxx>:
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@xxxxxxxxxxxxxxxxx
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
==============================================================================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
