Oracle sequence cache aging too often

Question

my asp.net application uses some sequences to generate tables primary keys. Db administrators have set the cache size to 20. 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

Subtype

So your ids have gaps. So what? I don't see the problem. – Taught

while gaps shouldn't be a problem, wasting 95% of the values is just that: waste – Depositor

to be clear : gaps don't worry me that much, i knew sequences might have gaps. – Subtype

Answer 1

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)

Taught

Since this is a test environment, I'd also be suspicious that the database was being restarted every night. Assuming that is not the case in production, this may be an environmental issue. – Intramundane

that's correct, the application is essentially a large form broken into ajax pieces. The test i've made today show it switches between two cached sequences. Apart being miserly and trying to save numbers, my fear was my use of sequences had some programming flaw. – Subtype

Answer 2

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

---

Marcille

Answer 3

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.

Asocial

Answer 4

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.

Depositor

The sequence cache is in the SGA ("the sequence cache [is stored] in the System Global Area (SGA).") A quick test of creating new sessions indicate that they are concurrent (that is with they increment as expected in both sessions using the same cache). let me know if you have different results if you test that, but it appears that the cache just times out due to inactivity to start a new count as opposed connection pooling issues. – Marcille

Read the link you provided, so I guess you are correct ... wonder where I picked up that false wisdom :-/ – Depositor