Re: Oracle-sequence and NOORDER

From: ddf <oratune_at_msn.com>
Date: Thu, 20 Sep 2012 07:07:23 -0700 (PDT)
Message-ID: <959a6371-a3c2-4387-b2ce-2d40a961326c_at_googlegroups.com>

On Thursday, September 20, 2012 4:50:02 AM UTC-6, dombrooks wrote:
> > Is it possible to create a test-case where they actually do come out-
>
> > of-order?
>
> If you use RAC and request sequence numbers on different nodes then yes - each node has its own cache.

Provided that the sequence uses the default of NOORDER (which should be obvious but one can't rely upon that presumption given the original post).

Creating the sequence ORDER will ensure that all sequence numbers appear in order of the request made to generate them in a RAC/Parallel Server environment [multi-node database] -- as an example:

fnorp_seq next value currently 157, sequence created ORDER

Node 1 (current time):

insert into plasmatron(frink, storbo, yazschutz) values ('NALZEEMY', fnorp_seq.nextval, 'PEEFERNEEM');

fnorp_seq.nextval == 157

Node 2 (17 seconds later than Node 1 request):

insert into plasmatron(frink, storbo, yazschutz) values ('QUEEPI', fnorp_seq.nextval, 'HOSSENGROP');

fnorp_seq.nextval == 159

Node 3 (8 seconds later than Node 1 request):

insert into plasmatron(frink, storbo, yazschutz) values ('OTOREWP', fnorp_seq.nextval, 'UTTREQA');

fnorp_seq.nextval == 158

If the sequence was created NOORDER then Nodes 1, 2, and 3 would create their own cached values:

fnorp_seq created NOORDER CACHE 20, next generated value will be 93

Node 1 (current time):

insert into plasmatron(frink, storbo, yazschutz) values ('NALZEEMY', fnorp_seq.nextval, 'PEEFERNEEM');

fnorp_seq.nextval == 93, isolated cache of 20 sequence values

Node 2 (17 seconds later than Node 1 request):

insert into plasmatron(frink, storbo, yazschutz) values ('QUEEPI', fnorp_seq.nextval, 'HOSSENGROP');

fnorp_seq.nextval == 133, isolated cache of 20 sequence values

Node 3 (8 seconds later than Node 1 request):

insert into plasmatron(frink, storbo, yazschutz) values ('OTOREWP', fnorp_seq.nextval, 'UTTREQA');

fnorp_seq.nextval == 113, isolated cache of 20 values

Note that in this case Node 1 has a sequence cache consisting of values 93 - 112, Node 3 has a cache consisting of values 113 - 132 and Node 2 has a cache consisting of sequence values 133 - 152 -- this is the result of NOORDER. It does not affect the order of the values from the sequence cache, it DOES affect whether each node gets its own cache or works from the global cache.

David Fitzjarrell Received on Thu Sep 20 2012 - 09:07:23 CDT