Oracle sequences

Fact: Our sun will run out of hydrogen in about 5 billion years (that’s 5 000 000 000). Remember this, and not only because at that point it starts expanding and will eventually envelop the earth.

Fact: The default maximum value for an oracle sequence is 1 octillion (that’s 1 000 000 000 000 000 000 000 000 000).

Fact: Often I see that people create multiple oracle sequences in their applications e.g. SEQ_PRODUCTS, SEQ_EVENTS, SEQ_REQUEST etc. I’m wondering if this is really necessary.

So: Let’s imagine that you build a really HIGH traffic system that processes about 100 million transactions per day, and let’s assume that this eventually leads to needing 10 billion sequence numbers per day. Every day. How many days will our sequence last?

The math for this is really simple: 1027 / 1010 = 1017 days

1017 days, that’s about 2.714 years = 270 000 000 000 000 = two hundred thousand billion years. (still remember how long before the sun runs out of hydrogen?).

So why ever bother creating multiple sequences ? One argument could be performance, a heavily used sequence can suffer from performance problems due to contention. In this case however, Oracle allows you to cache a predefined number of sequence values upfront. The downside of this that the unused numbers in the cache are lost when the database is restarted.

So, let’s assume we configure a sequence cache of 10 million (10 000 000) values, and we restart our database about COUGH 1 000 times per day . Do the math, the sun will have still ran out of hydrogen before our sequence hits its limit.

If anyone can give me a good reason why you should ever use more than 1 oracle sequence object in your application i’d like to hear it.

No comments: