This discussion is archived
7 Replies Latest reply: Dec 10, 2012 7:03 PM by onedbguru RSS

Sequence not in order in RAC ?

877720 Newbie
Currently Being Moderated
I created a sequence in a 2-nodes RAC:


create sequence SEQ_STEP
minvalue 1
maxvalue 9999999999999999999999999999
start with 30000000
increment by 1
cache 20
order;


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


Why?
  • 1. Re: Sequence not in order in RAC ?
    959717 Newbie
    Currently Being Moderated
    is there some time difference in your instances
  • 2. Re: Sequence not in order in RAC ?
    StenW Newbie
    Currently Being Moderated
    RAC nodes have their own subset of the sequence in something called a sequence cache.
    So 30324128 was first in the cache for the other node .. and shortly after first node "consumed" 30324127 etc.
  • 3. Re: Sequence not in order in RAC ?
    Victor Armbrust Oracle ACE
    Currently Being Moderated
    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.

    ---------------

    Victor
  • 4. Re: Sequence not in order in RAC ?
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    Are you using NOCACHE NOORDER? It is the best option for RAC config.
    Probably you mean CACHE NOORDER option.

    Best regards,
    Gennady
  • 5. Re: Sequence not in order in RAC ?
    Victor Armbrust Oracle ACE
    Currently Being Moderated
    owwps.. Yeah. correct.. (in relation to RAC). However I try to use NOCHACE+NOORDER when it is possible

    tks
    Victor
  • 6. Re: Sequence not in order in RAC ?
    LaserSoft Journeyer
    Currently Being Moderated
    Hi

    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.

    Thanks
    LaserSoft
  • 7. Re: Sequence not in order in RAC ?
    onedbguru Pro
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points