RE: Determine sequence cache size in RAC

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <adar666@xxxxxxxxxxxx>, <denis.sun@xxxxxxxxx>
  • Date: Mon, 2 May 2011 06:41:17 -0400

In addition to Yechiel's fine suggestions, if you search up joel goodman
sequences you'll find "Tell RAC to Leave Your Leaves Alone", "Managing
Sequences in a RAC Environment", and "Keep Your Sequences in Order" all of
which contain useful information.


If your operational requirements require further fine tuning, you can
hermitize your sequence definitions (my coined usage: hermitize - to put a
row alone in its own block [all by itself, like a hermit]).


Without sequence hermitization, your original question cannot have a
meaningful answer, because the collision of needing to do an "all instances"
coherent update for a block grows geometrically with the coincidence of
"hot" ordered sequence row definintions falling within the same block. With
sequence hermitization, you still won't have a rule of thumb, but you will
be able to create a plausibly reliable test to measure the rate of
transactions up to which you are not paced by sequences. If that rate is not
sufficient, you will know you either need further study of your design, a
relaxation of your sequence ordering rules, or a way to further increase the
rate of sequence updates beyond out of the box capabilities.






From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Yechiel Adar
Sent: Monday, May 02, 2011 5:49 AM
To: denis.sun@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Determine sequence cache size in RAC


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

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

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?




Other related posts: