I need to generate a unique number to attach it to the metadata of an object when a new object is created. The object metadata only has to be set from code using the framework library as the object is internally linked to multiple tables in the database and updating a column directly is not advised and risky. For this purpose, I am trying to use the Oracle DB sequence and use the nextval as the unique reference number. But to make this sequence generation more parallel, I wanted to introduce 10 sequencers with increments of 10 and starting with 1,2,3..,10 respectively, so that no sequence will generate a duplicate number. The idea is when 10000 objects are created at a time, I can split the load into 10 different sequences and process them in 10 batches in parallel.
But when I implemented this solution I can observe the single sequence implementation is more efficient than dividing into 10 different sequences.
The observation goes like this.
CASE 1:
Generation of 10 sequences from a single Sequencer took (10 sequence numbers from the same sequencer) - ~400 ms
Generation of 10 sequences from 10 separate sequencers took (1 number from each sequencer) - ~40 ms
Case 2:
Generation of 1000 sequences from a single Sequencer took (1000 sequence numbers from the same sequencer) - ~4600 ms
Generation of 1000 sequences from 10 separate sequencers took (100 numbers from each sequencer) - ~4200 ms
So the single sequence though appeared ten times slower, as the parallel requests increased, it managed to perform better whereas the multiple sequence implementation scaled linearly by 100 times and took the almost same time as a single sequence for 1000 sequence generations.
the cache size of all the sequencers here is the same and set to 100.
I would like to understand, do multiple sequencer implementation could help in parallel processing as I thought? If not, please explain why? and if it can, then where am I implementing this wrongly?
Thank you very much in advance for your time on this :)
Your solution seems needlessly complex. Simply increase the cache size of the one sequence so that it holds at least as many values as you might need in a single burst.
Querying multiple sequences will introduce a lot more overhead to the database kernel since each sequence and its cache must be loaded and maintained in memory, each query must be parsed and run separately, and those separate queries on separate objects will consume more of the library cache. Much more expensive than compiling one query and executing it more times with a properly tuned cache. In the end it will cause resource contention and not scale, and I would NOT advise going that route.