I created a sequence in a 2-nodes RAC:
create sequence SEQ_STEP
start with 30000000
increment by 1
and I put a trigger in my table, before insert I will use SEQ_STEP.nextval to set as primary key.
but when I start my application for some time, I saw a lot of sequence generated not in order like below:
30324126 2012/12/1 8:13:16
30324128 2012/12/1 8:13:18 <=this record is inserted before the next one, but it got a bigger sequence value.
30324127 2012/12/1 8:13:23
Are you using NOCACHE NOORDER? It is the best option for RAC config.
For your convinience I suggest you to read the MOS NOTE: RAC and Sequences [ID 853652.1]
There's a lot of influence using Sequences in RAC, be aware of it.
Just a preview:
1. Sequence setup
Different sequence setups are available.
a. CACHE + NOORDER
This setting has the least performance impact on RAC, and it is the default when creating a new sequence wihtout options. Each instances caches a distinct set of numbers in the shared pool. Sequences will not be globally ordered, and bigger gaps can occur when the shared pool is refreshed (e.g. instance shut down) like on single instance databases.
b. CACHE + ORDER
Each instances caches the same set of numbers. Ordering of sequence numbers is guaranteed, and bigger gaps can occur. Performance is better than with NOCACHE sequences. Gaps in the sequence numbering occur when the sequence cache is lost e.g. any shared pool flush or instance shutdown like an single instance databases.
c. NOCACHE + NOORDER
Use this setting when e.g. government regulations or laws legally require sequence numbers with limited gaps (see point 2). Ordering is not guaranteed. It has a better performance than NOCACHE / ORDER.
d. NOCACHE + ORDER
Use these settings when ordered sequences are required; no gaps will occur (except for point 2 examples) and ordering is guaranteed. This setting though has the most negative performance impact on RAC.
In Always RAC we have to use NOCACHE + NOORDER. Becuase when genarating sequence one instance second instance will try for same. So we have to give these two options so that second instance will genarate some number without order. So we will not get any locking issues or performance issues.
As stated, one of the application "problems" that can arise would be to "require" ordered sequences. This is the worst option for RAC specifically because of the multiple instances trying to do inserts concurrently. I would typically never used ORDERED sequences in a RAC environment. Requiring sequential ordered sequences are problematic even in a single node database. NOCACHED is also very costly in that for EACH request, you must lock+increment the sequence serially. This is extremely expensive in terms of locking.