To meet some odd business requirements, I'm going to have to implement my own sequence-like counters. I'm going to make a first cut of this in the obvious way, but I would like to understand a bit more about how Oracle implements sequences. For example, can they use latches instead of locks?
I've been unable to find much about this on the web, so pointers to docs as well as insight from your personal experience welcome!
(10g RAC, if it matters)
I don't think Oracle has published the internal workings of sequences, but Jonathan Lewis has written some detailed analysis of how they work here. From that document:
Because the mechanism is internal to Oracle it is very efficient and bypasses the normal locking contention that appears with the traditional end-user coded ‘tables of sequence a sequence object: ‘give me the next available number’ and ‘remind me what that was again’; these are the nextval and currval calls respectively.
The nextval request goes to the global cache to get the next available sequence value and copies it to the session’s local memory.
Here's a good set of issues answered: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11%5FQUESTION%5FID:6575961912937
Do you want/need a gapless sequence of numbers? Read this: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11%5FQUESTION%5FID:4343369880986
I think you need to explain why you would need to implement your own sequence. If we know the problem you are trying to solve we may be able to give you an answer so you would not have to do your own custom sequence.