Re: Order of sequence number generation

Sabri,

Oracle caches sequence numbers in the SGA and the default cache size is 20. If, for any reason, the cache must be flushed, then the sequence number will jump in the manner you observed. The cache is flushed during database shutdown and can also be flushed if there is insufficient room in the SGA for the sequence numbers. The number of sequence cache entries is controlled by the parameter SEQUENCE_CACHE_ENTRIES - you may want to increase this.

You may also wish to reconsider the use of the ORDER clause: Oracle will generate sequences in order by default, and the ORDER clause is really meant for Oracle Parallel Server environments. When you use the ORDER clause, each sequence get is associated with a data dictionary lookup and update. This will slow down your sequences markedly, and might even have something to do with your missing sequences (though I couldn't replicate that).

Regards,

Guy Harrison
gharriso@werple.net.au || http://werple.net.au/~gharriso/ || 6149 377 964

Sabarinath Nair wrote in message <344E51DE.5E125716_at_mit.edu>...
>Hello,
>
>I have a sequence created using the foolowing SQL.
>
>create sequence SEQ_MESSAGE_ID INCREMENT BY 1 START WITH 100 maxvalue
>999999999999 ORDER;
>
>When I use this sequence, thru a stored procedure, the sequence numbers
>which are generated are not incrimented by 1. instead it gets increments
>of 20. 1, 21, 42 etc...
>
>Does any one know how to get around this...
>
>
>Thanks
>Sabari
>
Received on Sun Oct 26 1997 - 00:00:00 CDT