Re: Order of sequence number generation

Sabarinath Nair <snair_at_mit.edu> wrote:
>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...
>
>

hi,

two possibilities:

if you are doing something like:

     select seq_message_id.nextval from dual;

check that dual only has one row on it. dual can sometimes accumulate extra rows. You might find you've got 20 rows on dual. ( This situation is unlikely, but worth checking. )

Secondly, I've had similar problems with numbers being jumped when using sequences. This is caused by caching problems. Your sequence is cached, by default. This means that a block of numbers are held in memory. If your initora parameter sequence_cache_entries is less than the number of sequences used on your database, your sequence will be periodically flushed from the shared_pool_area. When that happens, the unused numbers in the currently cached range for your sequence will be lost. I suspect this is whats happening. ( especially as the default cache size when creating a sequence is 20! ). You should increase your value of sequence_cache_entries. This will minimise the problem. If you must guarantee never to have jumps, you'll need to create your index with the NOCACHE option.

HTH, Dave.

-- 

To reply by email, remove the "no-spam" bit from my email address.