7 Replies Latest reply on Dec 11, 2012 3:03 AM by onedbguru

    Sequence not in order in RAC ?

      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

      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

        • 1. Re: Sequence not in order in RAC ?
          is there some time difference in your instances
          • 2. Re: Sequence not in order in RAC ?
            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
              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.


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

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

                  • 6. Re: Sequence not in order in 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.

                    • 7. Re: Sequence not in order in RAC ?
                      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.