Oracle sequence cache aging too often

my asp.net application uses some sequences to generate tables primary keys. Db administrators have set the cache size to 2开发者_如何学Go0. Now the application is under test and a few records are added daily (say 4 for each user test session). I've found that new test session records always use new cache portions as if the preavious day cached numbers had expired, losing tenth of keys everyday. I'd like to understand if it's due to some mistake i might have made in my application (disposing of tableadapters or whatever) or if it's the usual behaviour. There are programming best practices to take into account when handling oracle sequences ?

Since the application will not have to bear an heavy load of work (say 20-40 new records at day), i was tinking if it might be the case to set a smaller cache size or none at all. Does sequence cache resizing implies the reset of current index ?

thank you in advance for any hint

---

The answer from Justin Cave in this thread might be interesting for you:

http://forums.oracle.com/forums/thread.jspa?threadID=640623

In a nutshell: if the sequence is not accessed frequently enough but you have a a lot of "traffic" in the library cache, then the sequence might be aged out and removed from the cache. In that case the pre-allocated values are lost.

If that happens very frequently to you, it seems that your sequence is not used very often.

I guess that reducing the cache size (or completely disabling it) will not have a noticable impact on performance in your case (also when taking your statement of 20-40 new records a day into account)

---

Oracle Sequences are not gap-free. Reducing the Cache size will reduce the gaps... but you will still have gaps. The sequence is not associated to the table by the database, but by your code (via the nextval on the insert via trigger/sql/pkg api) -- on that note you may use the same sequence over multiple tables (it is not like sql server's identity where it is associated to the column/ table)

thus changing the sequence will have no impact on the indexes.

You would just need to make sure if you drop the sequence and restart it, you 'reseed' to the +1 of the current value (e.g. create sequence seqer start with 125 nocache;)

, but

If your application requires a gap-free set of numbers, then you cannot use Oracle sequences. You must serialize activities in the database using your own developed code.

but be forewarned, you may increase disk IO and possible transaction locking if you choose not to use sequences.

The sequence generator is useful in multiuser environments for generating unique numbers without the overhead of disk I/O or transaction locking.

to reiterate a_horse_with_no_name's comments, what is the issue with gaps in the id?

---

Edit also have a look at the caching logic you should use located here: http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/views002.htm#i1007824

---

---

If you are using the sequence for PKs and not to enforce some application logic then you shouldn't worry about gaps. However, if there is some application logic tied to sequential sequence values, you will have holes if you use sequence caching and do not have a busy system. Sequence cache values can be aged out of the library cache.

You say that your system is not very busy, in this case alter your sequence to no cache. You are in a position of taking a negligible performance hit to fix a logic issue so you might as well.

---

As people mentioned: Gaps shouldn't be a problem, so if you are requiring no gaps you are doing something wrong. (But I don't think this is what you want).

Reducing the cache should reduce the number and decrease the performance of the sequence especially with concurrent access to it. (which shouldn't be a problem in your use case).

Changing the sequence using the alter sequence statement (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2011.htm) should not reset the current/next val of the sequence.