Re: SEQUENCE_CACHE_ENTRIES obsolete?

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. Received on Tue May 15 2001 - 19:09:47 CDT