Re: index contention in RAC
- From: Andrey Kriushin <Andrey.Kriushin@xxxxxxxx>
- To: johan.eriksson@xxxxxxxxxxxx
- Date: Mon, 24 Apr 2006 01:25:29 +0400
Johan,
Good points from others about sequences and application partitioning
methods - these are usual, clear and straightforward ways to deploy an
app in RAC. So never believe to marketroids and try to eliminate
contention by application design. Of course, cache fusion, dynamic
resource re-mastering etc make life better but still not that easy for
intensive OLTP in RAC :-(.
I'd suggest you to check (via V$SESSION_WAIT par ex) which blocks are
the main source of contention. Parameters for 'gc buffer busy' are just
the same as for 'buffer busy', so you can see if some particular blocks
appear on the list frequently. At least it would be clear if this is a
block in seq$ table or some blocks of the index/table or probably BMB of
the index segment(s)? As for BMB - I've seen hard block contention for
BMB in similar application (high insert rate, reverse index) even in a
signle(!) instance environment when BMBs became almost full. It looked
like a spike in CBC latch and buffer busy on BMBs.
One important thing about reverse key indexes: they are good while they
are small. Otherwise you'll have your buffer cache populated by
relatively "cold" leaf blocks of this index, which may cause additional
PIO and as a result - buffer busy from another instance trying to access
the same block while the buffer is in MREAD mode. Definitely, additional
partitioning (by instance id) would solve the problem much better than
reverse key index.
Some additional info:
- STATSPACK.SNAP(i_snap_level=>7) collects and reports statistics at
segment level (CUrrent and CR) so should do ADDM/AWR.
- Not sure if V$WAITSTAT accounts for 'gc buffer busy' the same way as
for just 'buffer busy'. Does anybody around knows?
HTH,
- Andrey
The tablespaces are locally managed and ASSM.
...
The problem we have are large amounts of GC buffer busy, since the
primary key is generated by an sequence I have made the index reverse to
eliminate some of the buffer busy events and that helped alot but the
major waiting is still on gc buffer busy and I want to know if there is
more I can do to minimize/eliminate this?
...
I did have default 20 and incresed it to 2000. The select nextval
dropped considerable to almost nothing.
The inserts now take 98% of the load and the gc buffer busy is 82% of
this.
--
//www.freelists.org/webpage/oracle-l
Other related posts: