Re: Caching in Sequence generators

In general you should not be skipping blocks of values, but it can happen under some circumstances (such as a database crash, or export/import cycle). The skip would also be a quantity up to the cache size, rather than exactly the cache size.

The cache operates by updating sys.seq$ table, and keeping an in-memory track of that value until calls to nextval reach it - at this point seq$ is updated again and the process repeats. This is why a crash can cause a jump.

If you go to NOCACHE (or ORDERED, I think) then seq$ is updated every time nextval is used - the overhead can be quite significant in very busy systems as both rollback and redo are generated and the recursive transaction has to commit - if you are running to dozens of calls per minute, then the performance impact can be quite noticeable.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

D. Oakley wrote in message ...
>(Running Oracle 7.3 on Unix)
>
>Hi,
>
>I was having a problem with a sequence generator that i was using in that
it
>seemed to be skipping numbers in blocks of 20. I presumed this was being
>caused by the default cache setting of 20. Firstly, is this a safe
>presumtion?
>
>Secondly, what effect will there be on performance of the sequence
generator
>if I set the caching to NOCACHE?
Received on Tue Apr 20 1999 - 14:09:37 CDT