Re: SEQUENCE_CACHE_ENTRIES obsolete?

"Robert William Vesterman" <bob.work_at_vesterman.com> wrote in message news:2sg3gtkajfgrreq10i2f1qll94cdd77sbo_at_4ax.com...
> On Mon, 14 May 2001 19:19:12 +0200, "Sybrand Bakker"
> <postbus_at_sybrandb.demon.nl> wrote:
>
> >
> >"Robert William Vesterman" <bob.work_at_vesterman.com> wrote in message
> >news:8dsvftcc7hjaseqfpvs74esouado707teg_at_4ax.com...
> >> I've been having a problem where our sequences often skip to one more
> >> than the next multiple of twenty, i.e. 1, 21, 41, 61, 81, 101, 121,
> >> etc. You'll sometimes see a few numbers in a row, but it quickly goes
> >> back to skipping, e.g. 141, 142, 143, 161.
> >>
> >> Looking into this issue, I found that it's perfectly consistent with
> >> what would happen if SEQUENCE_CACHE_ENTRIES was too low. Plus, it
> >> seems that it really was too low, because the default value (10) is
> >> far less than the number of sequences we use, and we constantly switch
> >> from one sequence to another.
> >>
> >> So, I suggested upping SEQUENCE_CACHE_ENTRIES. They tried this, and
> >> got "ORA-25138: SEQUENCE_CACHE_ENTRIES initialization parameter has
> >> been made obsolete".
> >>
> >> I've been looking on the web, and I haven't seen anything that says
> >> what its functionality has been replaced by... does anyone know?
> >>
> >> Or is there some other reason why my sequences would be skipping in
> >> that manner?
> >>
> >> Thanks,
> >>
> >> Bob Vesterman.
> >
> >This has nothing to do with the SEQUENCE_CACHE_ENTRIES parameters, as
 that
> >determines or determined, the number of sequences cached, not the number
 of
> >units cached per sequence.
> >Your problem occurs when you shutdown and restart the database, and still
> >have numbers in cache: you will loose them. If you rollback a transaction
> >you will loose that number. Etc.
> >If you really don't want that:
> >alter sequence <sequence name> nocache order;
>
> No.
>
> I'm not talking about shutting down and restarting the database. This
> happens in the normal flow of things, not just when the db is
> stop/started.
>
> Moreover, it *does* have to do with SEQUENCE_CACHE_ENTRIES (or at
> least it's consistent with SEQUENCE_CACHE_ENTRIES being too small). I
> understand that SEQUENCE_CACHE_ENTIRES is the number of sequences
> cached, not the number of units cached per sequence. That's exactly
> the point.
>
> I'll explain in more detail:
>
> We have many more than the default number of sequences (10) that
> Oracle will cache. We switch from one sequence quickly. So, we use
> ten sequences, each of them gets twenty numbers cached, and then we
> use an eleventh sequence.
>
> Oracle caches twenty numbers for this sequence, and drops one of the
> original ten sequences that was cached, since it can only cache ten
> sequences at a time. Any unused, cached numbers in that sequence are
> now gone forever.
>
> We then use a 12th sequence, and another group of nearly twenty
> numbers gets lost. And so on.
>
> If we upped SEQUENCE_CACHE_ENTRIES to allow Oracle to cache as many
> sequences as we actually have, it wouldn't have to throw away
> sequences that it has cached, and this problem would be resolved.
> Unfortunately, SEQUENCE_CACHE_ENTRIES is now obsolete, and I can't
> find anything that replaces its functionality. So, again, my
> question:
>
> What replaces SEQUENCE_CACHE_ENTRIES' functionality?
>
> Bob Vesterman.

Usually the functionality doesn't really disappears. I assume right now the part of the SGA where the sequences are, is now dynamically expanded. If this answer doesn't satisfy you, I have to advise you to get in touch with Oracle Technical Support.
They don't bite, and they might even know more about their product than a free of charge newsgroup.

Regards,

Sybrand Bakker, Oracle DBA Received on Sat May 19 2001 - 02:08:56 CDT