Re: Caching in Sequence generators

Great! that solves that one then.

Thanks guys.

> 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 - 11:04:29 CDT