Re: Caching in Sequence generators

Yes, simply stopping and starting the database is enough to loose the Oracle sequences. Once one sequence is taken for a particular instance session, then any cached sequences will be lost when the database is closed down normally.

Think of it this way;

SELECT SEQ.NEXTVAL from DUAL;

will effectively do this;

select value
from seq$
where name = 'SEQ';

update seq$
set value = value + cache
where name = 'SEQ'

Then the value is returned to the oracle session.

Rgds
Andrew

"D. Oakley" wrote:

> DB has not crashed! However, it has been stopped for exports and backups a
> few times.. would stop/starting the instance be enough to make it discard
> the cached values and start over with a new set?
>
> TIA.
>
> > 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 Thu Apr 22 1999 - 07:24:28 CDT