Why Sequence jumps?

[Wassim]

Hi,
I'm using Sequences (as many of you told me to do, for generating a
primary keys in some tables) Strange phenomenen i've noticed is that in
some cases counter jumps from 21 to 40 or 11 to 34 ? this is source of
my sequences :

create sequence mvt_seq
start with 0
increment by 1
nomaxvalue
minvalue 0
nocycle
order (cos order is important for me!)

Is there any problem with this?
Thanks for help!

Wassim.
e-mail: net...@francemel.com

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

[Mark Malakanov]

It is wide-known fenomena.
The sequences uses cache of numbers for fast work.
The default value for it is 10.
The all 10 numbers will loaded into cache when user process get first
NextVal.
And if an user process falls down, gotten numbers dissappears.
Therefore the gaps appears.

You can regulate the sequence cash by
ALTER SEQUENCE CACHE ...

Also you would need to increase "sequence_cache_entries" parameter
if used many sequences in database at the same time.

[ If the value for SEQUENCE_CACHE_ENTRIES is too low, Oracle might skip
sequence values, as in the following scenario: assume you are using five
cached sequences, the cache is full, and SEQUENCE_CACHE_ENTRIES = 4. If four
sequences are currently cached, then a fifth sequence replaces the least
recently used sequence in the cache and all remaining values (up to the last
sequence number cached) in the displaced sequence are lost. ] Oracle Doc.

Mark Malakanov

Wassim <net...@francemel.com> wrote in message
news:7l5kd2$qi9$1...@nnrp1.deja.com...

[rpd...@cyberus.ca]

When nocache is not specified in the create sequence, the default is
20...this why you'll notice a jump in the numbers.. Most often it
doesn't matter so long at it's they're unique, however I've seen some
sites use sequences in such a way that need to be sequential.....

Here's how you can fix it..

alter sequence mvt_seq nocache;

Good luck,

Robert Prendin

On Sun, 27 Jun 1999 16:44:20 GMT, Wassim <net...@francemel.com>
wrote:

>Hi,
>I'm using Sequences (as many of you told me to do, for generating a
>primary keys in some tables) Strange phenomenen i've noticed is that in
>some cases counter jumps from 21 to 40 or 11 to 34 ? this is source of
>my sequences :
>
>create sequence mvt_seq
>start with 0
>increment by 1
>nomaxvalue
>minvalue 0
>nocycle
>order (cos order is important for me!)
>
>Is there any problem with this?
>Thanks for help!
>
>Wassim.
>e-mail: net...@francemel.com
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

#----------------------------------
# RP Data Solutions Inc.
# Oracle Database Support Services
# Ottawa Canada +1(613)791-0599
#----------------------------------

[Tim Romano]

In addition to cache issues already discussed in another reply,a
transaction when rolled back does not give back any sequence numbers to
the sequence(s) for reuse. So you couldn't use a sequence to provide
numbers for, say, a check register. Its purpose is simply to guarantee a
unique value.
Tim Romano

[Roger Jackson]

Wassim,

Here is a list of possible reasons why values of sequence numbers may
be lost.

1. Individual sequence numbers can be skipped if they were generated and
used
in a transaction that was ultimately rolled back. The sequence is
incremented independent of the transaction committing or rolling back.
This will occur regardless of the sequence being cached.

2. The same sequence can be used for one or multiple tables. It is
possible that individual sequence values may appear to be skipped
because
it is being used for multiple tables.

3. If a sequence is cached, the unused cached sequence values may be lost
if
there is a system failure (shutdown abort or system crash).

4. If the value of "SEQUENCE_CACHE_ENTRIES" "init.ora" parameter is too
low,
it is possible to skip sequence values. If the number of cached
sequences
is greater than "SEQUENCE_CACHE_ENTRIES", then a sequence cache may be
aged out of the SGA. If a request is made for a sequence that is not in the
cache and there are no free entries, the oldest on the LRU list is deleted
and replaced with the newly requested one. All of the remaining values

in
the displaced sequence are lost.

5. In 7.3 the functionality of the caching of sequences was changed. Prior
to 7.3, the sequence cache was kept in the row cache. In 7.3+ the
sequence cache was moved to the library cache. Oracle Bug 432251
identified the cached sequence was being aged out of the library cache too
rapidly. This
bug is fixed in 7.3.4.

I hope this helps.

Wassim <net...@francemel.com> wrote in message
news:7l5kd2$qi9$1...@nnrp1.deja.com...