PRB: Sequences losing cached entries

We recently upgraded several of our database instances to version 7.3.2.1.0 (from 7.0.15). Our database applications use sequences to provide surrogate primary keys. I have always created these sequences with a cache of between 20 and 50. This reduces disk IO (from the Server Admin Guide, pg 199:
"The CACHE option preā€“allocates a set of sequence numbers and keeps them
in memory so that sequence numbers can be accessed faster. When the last of the sequence numbers in the cache has been used, Oracle reads another set of numbers into the cache."). This has proven efficient and works well... up until the recent upgrade.

Since this upgrade most of my cached sequence numbers are getting flushed before ever being used (e.g. usually only the first 2-7 entries are being used from the 20-50 cached numbers, then the cache gets reloaded and we lose the rest of the cached entries).

The Oracle manual states:
"Oracle might skip sequence numbers if you choose to cache a set of
sequence numbers. For example, when an instance abnormally shuts down (for example, when an instance failure occurs or a SHUTDOWN ABORT statement is issued), sequence numbers that have been cached but not used are lost. Also, sequence numbers that have been used but not saved are lost as well. Oracle might also skip cached sequence numbers after an export and import; see the Oracle7 Server Utilities guide for details."

I can live with this. It is truly understandable behavior, but these events are not (generally) occurring when we lose our cached entries.

Further on the manual also states:
"The initialization parameter SEQUENCE_CACHE_ENTRIES sets the number of
sequences that may be cached at any time. If auditing is enabled for your system, allow one additional sequence for the sequence to identify audit session numbers.
If the value for SEQUENCE_CACHE_ENTRIES is too low, Oracle might skip sequence values, as in the following scenario: assume you are using five cached sequences, the cache is full, and SEQUENCE_CACHE_ENTRIES = 4. If four sequences are currently cached, then a fifth sequence replaces the least recently used sequence in the cache and all remaining values (up to the last sequence number cached) in the displaced sequence are lost."

We have set our SEQUENCE_CACHE_ENTRIES to a very high value (150 as I recall). The applications themselves only use 20-30 sequences (total number of non-oracle internal sequences in each database instance). How many sequences (approximately) does the RDBMS use internally to manage the data dictionary? Does this value (SEQUENCE_CACHE_ENTRIES=150) seem high enough?

I also understand that there is a possibility that cached sequence entries get flushed if the SGA fills up (using the LRU algorithm, they get aged out of the SGA). The details of the SGA for the 3 database instances where this behavior is occurring is:

SVRMGR> show sga

Total System Global Area       5894084 bytes
Fixed Size                       39696 bytes
Variable Size                  5018804 bytes
Database Buffers                819200 bytes
Redo Buffers                     16384 bytes

SVRMGR> show sga
Total System Global Area      13101900 bytes
Fixed Size                       39696 bytes
Variable Size                 10645564 bytes
Database Buffers               2252800 bytes
Redo Buffers                    163840 bytes

SVRMGR> show sga
Total System Global Area      24856136 bytes
Fixed Size                       39696 bytes
Variable Size                 11545400 bytes
Database Buffers              13107200 bytes
Redo Buffers                    163840 bytes

Given all of this information, does anyone know what might be causing the cached sequence entries to be lost so frequently? This behavior makes caching the sequences practically useless, since they are continually being reloaded anyway! Your help is greatly appreciated.

John Elliott
elliotjs_at_dmdcwest.fmp.osd.mil Received on Fri Dec 06 1996 - 00:00:00 CST