Re: index contention in RAC

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

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