Friday, April 20, 2007

Sequence numbers from a Oracle RAC cluster

Today, I faced a peculiar problem. Our application was using a Sequence to autogenerate unique numbers required for a particular functionality. When testing the application against a standalone Oracle server, the sequence numbers generated were in order.

But in the production evnironment, where we were using a Oracle RAC cluster, the sequence numbers returned were not in order..This was creating a lot of confusion. The reason was that by default sequences in 10g RAC are created without ordering. This is to reduce performance bottleneck as there would be synchronization requried across all nodes of the cluster. Each node creates a cache of the sequence as specified in the SQL.

If we need to maintain the order then we need to add the ORDER argument to the sequence.
Example of Sequence Syntax in Oracle RAC:

CREATE SEQUENCE "MY_DEV"."CON_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 111785 CACHE 500 ORDER NOCYCLE ;