RE: Determine sequence cache size in RAC

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 2 May 2011 06:41:17 -0400
Message-ID: <00fc01cc08b5$789c3e30$69d4ba90$_at_rsiz.com>

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.  

Regards,  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Yechiel Adar
Sent: Monday, May 02, 2011 5:49 AM
To: denis.sun_at_yahoo.com
Cc: oracle-l_at_freelists.org
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 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 - 05:41:17 CDT