Re: Determine sequence cache size in RAC

From: Yechiel Adar <adar666_at_inter.net.il>
Date: Mon, 02 May 2011 12:48:32 +0300
Message-id: <4DBE7DF0.5020307_at_inter.net.il>

Since nobody else related to this I will try: Sequences in RAC are handled separately by each instance. Each instance reads the sequence from the database and start allocating number until is it finish the cache range. Then it reads the sequence again and start allocating new numbers. Assuming cache size of 3:

RAC1 - first call - 1 (save next value = 4 in the database).
RAC2 - first call - 4 (save next value = 7 in the database).
RAC1 - second call - 2
RAC1 - third call - 3
RAC1 - forth call - 7
RAC2 - second call - 5

There also contention between the nodes when they need to update the next value for the sequence.

On RAC, where inserts are performed on both nodes it depends if you care about the order or you just want to make sure that each row has a different value.

1. Order is important - Define the sequence with ordered and big cache. This will cause global lock between the nodes and will degrade performence.
2. Order is not important - Just define with big cache.

Big cache minimize the contention for updating the sequence.

Yechiel Adar
Israel

On 21/04/2011 01:37, Denis wrote:
> Hi, listers,
> Is there a rule of thumb that relates sequence cache size to insert
> rate and number of instances? Anyone can share what the cache size is
> for your RAC's insert-intensive tables?
> Denis

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

Received on Mon May 02 2011 - 04:48:32 CDT