Sequences in Oracle 10g RAC

Posted in: Technical Track

Just recently I got a call from a developer. He had a table with a primary key populated by a sequence, a timestamp column with the current date and some other columns. He had a specific set of data that, when ordered by the primary key had out of order timestamps. He was puzzled how this could be. This is a RAC database and the sequence was created with the default values.

Not only the sequences cache was the default of 20, but it was “noordered”. Being “noordered” Oracle will not guarantee the order in which numbers are generated.

Example of “noorder” sequence in 10g RAC:

Session 1 on node-A: nextval -> 101
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 121
Session 1 on node-B: nextval -> 122
Session 1 on node-A: nextval -> 103
Session 1 on node-A: nextval -> 104

The sequence cache is in the shared pool, therefore sessions on the same node can share the cached entry, but sessions on different nodes cannot. I wonder why Oracle doesn’t make “ordered” the default for sequences.

So I explained to the developer how sequences work in RAC and how each node has its own “cache”.

We changed the sequence to “ordered” and increased the cache to 1000. Now selecting on either node gets the next number as he expected. I warned him that there would be some performance implications due to cluster synchronization. Him been a responsive developer, asked me what would be the impact, so I tested it out.

How does RAC synchronize sequences?

In Oracle 10g RAC, if you specify the “ordered” clause for a sequence, then a global lock is allocated by the node when you access the sequence.

This lock acquisition happens only at the first sequence access for the node (A), and subsequent uses of the sequence do not wait on this lock. If another node (B) selects from that sequence, it requests the same global lock and once acquired it returns the sequences next value.

The wait event associated with this activity is recorded as “events in waitclass Other” when looked in gv$system_event. So much for event groups, it couldn’t be more obscure. That view shows overall statistics for the session.

However if you look in the gv$session_wait_history it shows as “DFS lock handle” with the “p1” parameter been the object_id of the sequence. This second view has a sample of the last 10 wait events for a session.

In a SQL_TRACE with waitevents (10046 trace) it will be a “DFS lock handle” but in AWR or statspack reports it will be “events in waitclass Other”. So much for consistency.

How does that change our example?

Session 1 on node-A: nextval -> 101 (DFS Lock handle) (CR read)
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 103 (DFS Lock handle)
Session 1 on node-B: nextval -> 104
Session 1 on node-A: nextval -> 105 (DFS Lock handle)
Session 1 on node-A: nextval -> 106
(more selects)
Session 1 on node-A: nextval -> 998
Session 1 on node-B: nextval -> 999 (DFS Lock handle)
Session 1 on node-B: nextval -> 1000 (CR read)

The cache size also has some RAC synchronization implications. When the cached entries for the sequence are exhausted, the sequence object needs to be updated. This usually causes a remote CR (current read) over the interconnect for the block that has the specific sequence object. So a bit more activity here.

Test case:

create sequence test_rac;
  dummy number;
for i in 1..50000 loop
  select test_rac.nextval into dummy from dual;
end loop;


50 000 loops with cache = 20 (default)
1 node = 5 seconds
2 nodes at same time = 14 seconds
2 nodes at same time ordered = 30 seconds

50 000 loops with cache = 1000

1 node = 1.5 seconds
2 nodes at same time = 1.8 seconds
2 nodes at same time ordered = 20 seconds

With a smaller cache, the “noordered” still has as significant impact as every 10 fetches (cache 20 divided by 2 nodes fetching) it has to synchronize between the 2 nodes

The conclusion

By default sequences in 10g RAC are created without ordering. Beware of using applications that rely on sequences to be ordered and using it in a RAC environment.

Consider changing all user sequences to “ordered” as a precaution and increasing the cache size.

The default cache value is still very low and even not-ordered sequences will cause contention in a highly-active sequence even in non-RAC and causing an additional block exchange every 20 values in RAC.

For high volume insert operations where ordering is not performed on the value returned from the sequence, consider leaving the sequence “noordered” but increasing the cache size significantly.

Either way, the sequence parameters should be reviewed, as chances are, the defaults are not what you need.

I remember reading somewhere that in Oracle 9i the “ordered” clause in RAC was equivalent to “nochache”. I can’t imagine how bad that would be in concurrent selects from the same sequence.

It would be interesting if someone running 9i RAC performs the test case and I would appreciate if you post the results in the comments.

Interested in working with Christo? Schedule a tech call.

About the Author

An Oracle ACE with a deep understanding of databases, application memory, and input/output interactions, Christo is an expert at optimizing the performance of the most complex infrastructures. Methodical and efficiency-oriented, he equates the role of an ATCG Principal Consultant in many ways to that of a data analyst: both require a rigorous sifting-through of information to identify solutions to often large and complex problems. A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

19 Comments. Leave new

Thanks for the reminder about RAC and sequences, here are my test case results on, 2 node RAC, test query running on both nodes:
NoOrder –
Default (cache 20) 0.27 sec
Cache 1000 0.06 sec
Order –
Default (cache 20) 0.44 sec
Cache 1000 0.31 sec

Christo Kutrovsky
February 1, 2007 10:30 am

Alan, thanks for testing this out. How many loops did you have?


I thought CACHE and ORDER together were not supported in 9i
(is that not correct?)
Note: The parallel server does not support the CACHE ORDER combination
—– of options for sequence number generators in parallel mode.
Sequences created with both of these options are ordered but not
cached when running in the parallel server. If multiple instances
concurrently insert rows in a table using a sequence generator for
index, excessive contention can occur among instances for the
index blocks unless the index values are separated with an
algorithm such as instance_number*(1000000000)+sequence_number.

Christo Kutrovsky
February 1, 2007 12:58 pm

Vidya, where did you get that quote form?

“parallel server” is a term used for Oracle 8i. Starting 9i they called this “RAC”.


Chris , the quote was from metalink( Doc ID: Note:1031850.6 Type: REFERENCE )
If cache and ordering are supported together in RAC acheived by acquiring a global lock – are there any performance issues? we have been using nocache ordering in one of our RAC application (since it is not high volume, we decided that the sequence could access disk – however ordering was very very critical for the application and we were not sure if RAC cache/ordering kept the order intact. However for higher volume Applications we will have to cache sequences.).

your input will be greatly appreciated.

Christo Kutrovsky
February 2, 2007 1:50 pm

Vidia, I am guessing this note is for Oracle7 and no longer applies for 10g (and also 9i imo).

I am guessing this from the footer of this note:


Oracle7 Parallel Server Concepts and Administration

*** end ***

Are you using 10g RAC ? If yes, then I would expect similar to my test case. For sure “nocache” will be much slower then “cache+order”.

August 7, 2007 2:35 am

Hi Chris,

what happens to sequence values cached in SGA at the time of an instance crash (with cache option)? I read somewhere that you would loose values (sequence gap) but does that apply to a database restart or an instance crash? how can sequences be catered through the application (pre-generated) or using dbms_random or creating a procedure ? would appreciate your reply.


Cheap Kamagra

Great finding. However, our results draw a different conclusion. If you increase the loop size to 500,000 the size of the cache has no effect from 2000 to 20000. Performance is aweful when ordered.

Type Cache Order Loop size Time in Sec
1N 20 N 50,000 2.6
2N 20 N 50,000 11
1N 20 Y 50,000 3.3
2N 20 Y 50,000 28
2N 2000 Y 50,000 13
2N 20000 Y 50,000 11
2N 20000 Y 500,000 180
2N 2000 Y 500,000 160
1N 2000 Y 500,000 21
2N 2000 N 500,000 13
2N 20000 N 500,000 13
1N 20000 N 500,000 14

1N One Node
2N Two Node Simultaneous runs

Christo Kutrovsky
September 13, 2007 9:35 am


Cached sequence values are lost in an instance crash. They can also be lost during pressure for memory in the shared pool. You should not rely on not loosing numbers.

IF you need to have a no-gap type sequence, you will need to pre-populate a table and manage it there.
With a sequence, you could loose numbers simply by requesting the next number, and rolling back after that. So even a nocache can “loose” numbers in that sense.


I have always approached a SEQUENCE OBJECT as simply a way to guarantee uniqueness for a surrogate key – nothing else. Once you start adding expectations on SEQUENCEs that go beyond that, it will add overhead, complexity and risk.


Can any one tell me the procedure to change the cache size and sequence type? Is there any way to determine the cache size for a particular database? I know I am asking silly questions but I didn’t find appropriate material? A link to appropriate material will also be helpful?

Thanking you

Oracle Performance: DFS Lock Handle and Oracle Sequence |
August 29, 2009 1:42 am

I think there is a typo in the sentence below
“With a smaller cache, the “noordered” still has as significant impact as every 10 fetches (cache 20 divided by 2 nodes fetching) it has to synchronize between the 2 nodes”

Did you mean to say “ordered” instead of “noordered” ?

Christo Kutrovsky
April 4, 2011 2:35 pm

Hi TalkingHead,

I think it’s correct as written. What I mean is that even “noordered” sequence can have a significant overhead if they use the default cache size.

subrata saha
July 21, 2011 6:11 pm

cache+noredered option in RAC – does it gurantee order within an instance of RAC according to call to this Sequence

subrata saha

Christo Kutrovsky
July 24, 2011 8:45 am

Subrata, what does the official Oracle Documentation say about this?

Performance Tuning Basics: Oracle Common Wait events « SureshGandhi
September 30, 2012 7:50 am
RAC Performance: DFS Lock Handle + Row Cache Lock + ENQ: SQ Contention , hanged the rac instance. « SureshGandhi
November 21, 2012 8:50 am

Hi Christo,

You said that “With a smaller cache, the “noordered” still has as significant impact as every 10 fetches (cache 20 divided by 2 nodes fetching) it has to synchronize between the 2 nodes”. While it is true that even when a sequence is noordered a smaller cache value on a busy sequence can wreck havoc on the database instance and can be catastrophic in a RAC, however if I cache a sequence by 20 and if I have a two node RAC, will the cache be divided (10 each per instance) amongst the instances or eache instance will have its own private cache of 20 for this sequence. If indeed the cache is divided amongst the RAC instances then how will the cache be divided if I have sequence with a cache value say 11 ( or any odd/prime number for that matter.) ?


Leave a Reply

Your email address will not be published. Required fields are marked *